tcronin 95651 (6/24/2014)
actually she is right about the indexes however it does not look the the engine uses them correctly. I had a table variable with 200 rows an and index, and a temp table with the same data. Did a join another large table and did a comparison on performance, the table variable took 222,000 logical reads to get the data, the temp tables 112.
It will create an index and it will take into account that the index is unique as part of the optimization process, but because it still doesn't have statistics, as was stated above, it doesn't behave in the same way through the optimizer. That's the fundamental issue for table variables. It's what makes them useful and useless. They don't have stats, so no recompiles, which can be awesome, depending. But, they don't have stats, so really horrific execution plans, which can be really problematic.
There is a change in the cardinality estimation in 2014 for those who are interested which might make some table variables somewhat less problematic.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning