Why RID (Bookmark) lookup is not being shown in execution plan?

Question

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:

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

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

Answer 1

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.