I am trying to understand why using a table variable is preventing the optimizer from using an index seek and then bookmark lookup versus an index scan.
Populating the table:
CREATE TABLE dbo.Test ( RowKey INT NOT NULL PRIMARY KEY, SecondColumn CHAR(1) NOT NULL DEFAULT 'x', ForeignKey INT NOT NULL ) INSERT dbo.Test ( RowKey, ForeignKey ) SELECT TOP 1000000 ROW_NUMBER() OVER (ORDER BY (SELECT 0)), ABS(CHECKSUM(NEWID()) % 10) FROM sys.all_objects s1 CROSS JOIN sys.all_objects s2 CREATE INDEX ix_Test_1 ON dbo.Test (ForeignKey)
Populate a table variable with a single record and attempt to lookup the primary key and the second column by searching on the foreign key column:
DECLARE @Keys TABLE (RowKey INT NOT NULL) INSERT @Keys (RowKey) VALUES (10) SELECT t.RowKey, t.SecondColumn FROM dbo.Test t INNER JOIN @Keys k ON t.ForeignKey = k.RowKey
Below is the execution plan:
Now the same query using a temp table instead:
CREATE TABLE #Keys (RowKey INT NOT NULL) INSERT #Keys (RowKey) VALUES (10) SELECT t.RowKey, t.SecondColumn FROM dbo.Test t INNER JOIN #Keys k ON t.ForeignKey = k.RowKey
This query plan uses a seek and bookmark lookup:
Why the optimizer is willing do the bookmark lookup with the temp table, but not the table variable?
The table variable is used in this example to represent data coming through a user-defined table type in a stored procedure.
I realize the index seek might not be appropriate if the foreign key value occurred hundreds of thousands of times. In that case, a scan would probably be a better choice. For the scenario I created, there was no row with a value of 10. I still think the behavior is interesting and would like to know if there is a reason for it.
OPTION (RECOMPILE) did not change the behavior. The UDDT has a primary key.
@@VERSION is SQL Server 2008 R2 (SP2) - 10.50.4042.0 (X64) (Build 7601: Service Pack 1) (Hypervisor)