Toby Harman (1/2/2012)
I was surprised and disappointed to see that the inability to use SELECT... INTO was a disadvantage.Anything which takes that option away from developers is a good thing IMHO!
I have a rule for Table Variables which I am pretty strict about. If the developer can't tell me exactly how many records (to within 10) and that number is less than 1000 (reasonably small) records, the it should be a temp table.
You also missed one big advantage of a temp table. You can index it. You need to do this a bit carefully, because index names are unique, so if more than one process is going to run generating this temp table, then you need to add a "uniqueifier" to the index name (such as @@SPID).
Not quite. Index names do not need to be unique. Constraint names do. If you let the system build the constraint names on temp tables, you'll never have a conflict.
Also, through the use of constraints built at creation time, you can, in fact, have indexes on table variables.
Not that it'll matter to anyone, but the only places where are I use table variables is when I need for something to be "Rollback Proof" or I need a table structure in a function.
So far as SELECT/INTO goes, I've found it to be quite handy both in the area of performance and keeping reasonable log sizes especially in TempDB. I'm not sure why so many people are against the use of such a thing.
--Jeff Moden
Change is inevitable... Change for the better is not.