I will admit that sometimes I have been known to use temporary tables rather than table variables, because of what I thought were limitations of the table variable. Turns out, table variables can do almost anything that a temporary table can do. Plus, they have the added bonus of having better performance, especially if they are used in a stored procedure. The reason for this is that a stored procedure that contains a temporary table cannot be precompiled. A precompiled script typically executes must faster. Also, while table variables exist only in the same scope in which they are created, they can be returned and passed into a stored procedure by creating a User Defined Type. So the only major limitation of a table variable versus a temporary table is that transaction logs are not recorded for table variables. Therefore, you cannot use them in the scope of a transaction.  One other difference, of course, is that table variables automatically go out of scope, while temporary tables must be explicitly DROPPED.

One thing I must admit that it took me a while to figure out is how to join with a table variable. Seems like it would be easy. For example, support I want to update a temporary table with the results of a subquery:

UPDATE @Users SET billable = (SELECT TOP 1 au.billable FROM application_user au WHERE au.[user_id] = @Users.[user_id]) WHERE u.billable IS NULL

The example above results in an error on the highlighted line stating that I must declare @Users. One simple change to this statement resolve the issue very easily.

UPDATE @Users SET billable = (SELECT TOP 1 au.billable FROM application_user au WHERE au.[user_id] = u.[user_id]) FROM @Users u WHERE u.billable IS NULL

This was definitely one of my DUH moments!  Smile

Advertisements