Rudyx - the Doctor (6/3/2014)
What is wrong with table variables ?hmmm ...
table variables are not capable of having indexes (however they support constraints and primary keys)
no indexes mean:
- no index statistics for the optimizer
- no indexes or index statistics which translates to mean a table scan for all access
- no indexes also mean that inserts with a constraint cause a table scan for every insert (the more rows the longer the scan)
Oh, and the initial fallacy that they are memory resident (HAH) - try:
select name
from tempdb..sysobjects
where name like '#%'
as for holding data in a roll-back scenarirollbackis wrong about using regular temp tables ?
anyone else ? did I miss something ?
They fixed some of the index issues with table variables in 2014. But table variables could always have an index. You just couldn't create indexes on the table variable after the fact.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events