foursaints (9/12/2008)
There have already been a couple replies on this so I won't belabor too much, but you definitely can create a PK on a table variable, you just can't name it. You can also create an index and apply any appropriate attributes as well (unique, clustered, nonclustered, etc.). SQL Server won't let you name these objects for exactly the reason described: possibility of naming collisions.As to the questions about advantages/disadvantages, here's my take: The table varaiable will give you a performance boost if you can keep its contents skinny and short (translation, fewest possible columns and fewest possible rows). From what I've read and heard, SQL Server tries its best to keep the table var's data memory-resident and will only "spill-over" into tempdb if surpasses a certain threshold. It also does help to add an index or two to the table var if you are joining, sorting or grouping on column(s). Don't go hog wild with indexes or you'll make too much work for the engine and very quickly lose any advantage you may have gained. The other performance boost comes with the fact that it is not a persisted object. Because it is not included in the transaction it does not add write I/O to the log file. And because it automatically falls out of scope when the batch completes it is very quickly and efficiently deleted without executing an additional statement in your code.
I've used table var's quite a bit over the past couple years and am very happy with the performance. One of my most common uses is when I need to left join a table but I only need a subset of its data. I have had very good results when I load the subset into the table var and then left join that in the parent query.
Speaking of efficiency, please don't forget about table-valued functions! These puppies are executed in their own memory context and are extremely fast. I think that TVF's are one of the most under-utilized great features of SQL 2000/2005. If you're ever thinking you've got an application for a table variable but wish you could filter its contents, there's your answer.
Temp tables act the same as table variables when it comes to memory... they, too, are extremely fast when used properly... read Q4/A4 in the following link...
... which says...
Q4: Are table variables memory-only structures that are assured better performance as compared to temporary or permanent tables, because they are maintained in a database that resides on the physical disk?
A4: A table variable is not a memory-only structure. Because a table variable might hold more data than can fit in memory, it has to have a place on disk to store data. Table variables are created in the tempdb database similar to temporary tables. If memory is available, both table variables and temporary tables are created and processed while in memory (data cache).
Sure, sure... use of a Temp Table normally guarantees a recompile... there's a lot of other things like small changes in data that also guarantee a recompile. Expected lifetime of an execution plan on a busy system is about 5 minutes. AND, table variables are guaranteed to evaluate as a single row table... not always the best thing for an optimal execution plan.
Don't use either a Temp Table or Table Variable based on rules of thumb. Do some testing... both will surprise you under different circumstances.
--Jeff Moden
Change is inevitable... Change for the better is not.