Reputation
971
Top tag
Next privilege 1,000 Rep.
See votes, expandable usercard
Badges
1 11 25
Newest
 Caucus
Impact
~98k people reached

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
Apr
6
accepted How does SQL Server's optimizer estimate the number of rows in a joined table?
Apr
3
comment How does SQL Server's optimizer estimate the number of rows in a joined table?
Thanks @Mark, the trace flags confirmed that the statistics were coming from the indexes listed in the query plan, but that's the extent of it.
Apr
3
revised How does SQL Server's optimizer estimate the number of rows in a joined table?
Corrected errors in logic based on misunderstanding of meaning of density vector.