• 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.