2

I tried to examine RID (foremerly bookmark) lookup by creating a heap table:

CREATE TABLE [dbo].[CustomerAddress] 
( 
[CustomerID] [int], 
[AddressID] [int], 
[ModifiedDate] [datetime] 
); 
GO 
CREATE NONCLUSTERED INDEX x 
ON dbo.CustomerAddress(CustomerID, AddressID);

Then, I tried the following query to inestigate execution plan:

SELECT CustomerID, AddressID, ModifiedDate 
FROM dbo.CustomerAddress 
WHERE CustomerID = 29485;

But, using MSSMS I cannot see RID lookup in the execution plan:

enter image description here

I'm using SQL Server 2008R2 (version 10.50.4000.0) service pack 2.

PS: This question is based on Aaron Bertrand's article.

Alireza
  • 10,237
  • 6
  • 43
  • 59

1 Answers1

4

A table scan means SQL Server does not use your index. It reads from the "heap". A "heap" is the data storage for tables without a clustered index.

Since it does not touch the index at all, SQL Server does not need a RID lookup to go from the index to the heap.

The reason is probably that SQL Server estimates there might be more than +/- 100 rows for one customer. The optimizer will try to avoid a large numbers of lookups.

You could try again with an index on just (CustomerID), or by adding an AddresID to your where clause.

Andomar
  • 232,371
  • 49
  • 380
  • 404
  • Thanks. I tailored the data so that few number of rows are returned and the optimized finally decides to do RID lookup. – Alireza Aug 19 '14 at 18:00