Reputation
1,031
Top tag
Next privilege 2,000 Rep.
Edit questions and answers
Badges
1 14 25
Impact
~110k people reached

Jan
19
awarded  Notable Question
Jan
11
awarded  Good Question
Oct
29
awarded  Notable Question
Aug
6
awarded  Caucus
Aug
6
awarded  Constituent
Aug
5
awarded  Nice Question
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
@srutzky: yes, saw the same behavior with that as well...
Jul
28
accepted Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
@AaronBertrand: does the existence of statistics in the #temp table allow the optimizer to see the value that the query is joining against and thus use the statistics for the index on the real table to determine how many rows can be found?
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
@@VERSION is SQL Sever 2008 R2 (SP2) - 10.50.4042.0 (X64) (Build 7601: Service Pack 1) (Hypervisor)
Jul
28
revised Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
deleted 187 characters in body
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
@srutzky: unfortunately that didn't change the behavior.
Jul
28
revised Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
added 62 characters in body
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
Added a SQL Fiddle. As for table variables vs. temp tables, the table variable is coming to the stored procedure as a UDDT. So I want to avoid having to write the data again to tempdb in a temp table.
Jul
28
revised Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
added 62 characters in body
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
Both the table variable and temp table estimate 1 row. The scan estimates all rows in the table and the index seek estimates one row.
Jul
28
comment Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
The table variable represents a UDDT coming into a stored procedure. I am trying to avoid writing to a temp table because this code will be used on a large scale. Also, am curious to know the why of the behavior.
Jul
28
revised Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
added 48 characters in body
Jul
28
asked Why is table variable forcing an index scan while temp table uses seek and bookmark lookup?
Jul
21
awarded  Popular Question