Note: This is part of a series on Temporary Query Items.
Previously we’ve talked about Temporary Query Items, what they are, why they matter, and the various factors that might cause you to choose one over another. We’ve also already talked about our first TQI, temporary tables. Without further ado, it’s time for our next one – the Table Variable.
A Little History
Table variables have been present in SQL Server since at least SQL Server 2000. You should be able to make use of them everywhere.
Location, Location, Location
Table variables live in TempDB. There’s an ancient myth that says table variables only live in memory – that’s not entirely true. Like any other data set in use, a table variable may be cached in memory, but its actual home address is TempDB. As with temporary tables, this means that table variables can more easily cause TempDB contention than other TQIs.
Unlike temporary tables, table variables are local in scope. You can only use them in the originating connection.
How Long Does It Live?
Table variables don’t last very long relative to temporary tables, but they do have a fair amount of longevity. When you create a table variable, the data contained within will exist until the completion of the transaction. You can refer to it in multiple queries within one transaction, but it will disappear at the end of transaction.
Table variables do support clustered indexes, but ONLY when the index is defined in the variable declaration. This actually improves somewhat in SQL Server 2014, where we have the ability to define non-clustered indexes directly in our table create statements – and therefore, we are able to define non-clustered indexes on table variables there.
One thing that table variables do NOT support is column statistics. This can be a pain point due to the fact that the query optimizer can’t determine how many rows to expect out of a table variable, so it assumes that the number will always be 1. 1 is a terrible default, but it’s better than all the other potential defaults. Large result sets stored in table variables can lead to performance problems depending on the execution plan that is chosen.
Like temporary tables, when you create a table variable based on an existing data set, it is created as a separate physical copy of the data. Modifications to the table variable do not affect the original data source. This can be either a strength or a weakness , depending on your purposes for using a TQI.
But Does It Blend?
The data in a table variable is fully modifiable. However, modifications to the table variable result set do not automatically translate back to the original data used to populate it. This can definitely be either a point for or against your choice of TQI.
Show Me The Code!
Test Data Setup
I’ll be using a test table to do some demonstrations. You can create/populate it here:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15
The creation syntax for a table variable is a hybrid between a CREATE TABLE statement and a DECLARE statement – which makes sense, as a table variable is a table that happens to be a variable.
1 2 3 4
Note that you CANNOT do a SELECT INTO with a table variable.
Inserting into a table variable is just like inserting into a regular table. However, note that you have to do the INSERT in the same transaction as the CREATE.
1 2 3 4 5 6 7 8 9 10
Table variables are also updateable:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18
Unfortunately, you cannot index a table variable after creation. This means that prior to SQL Server 2014, you cannot add nonclustered indexes to table variables. You can, however, declare a table variable with a clustered or unique index on it:
1 2 3 4 5 6 7 8 9
It is also not possible to create statistics on a table variable. Therefore, the query optimizer will always estimate that there is 1 row in the table variable, and therefore it may make some very bad plan choices. You’ll find that a lot of people insist that table variables are bad for performance – this is usually the reason. A table variable with millions of rows is almost always going to be slower than about any other option.
Global vs Local
Table Variables are only local. They’re so local, actually, that their use is limited to the transaction they’re created in.
No really, only the creating transaction. The insert will fail:
1 2 3 4 5 6 7 8 9 10 11 12
Source Data Dependence
As with temporary tables, data set modifications are done independently of the original data. This should be pretty clear from the INSERT INTO syntax:
1 2 3 4 5 6 7 8 9 10 11 12 13 14
And here’s the cleanup code for this exercise:
We’ve taken some time to look at some attributes of Table Variables, including strengths and weaknesses. As with Temporary Tables, you can’t make a decision about using them in a vacuum. In particular, I’ve cautioned against the use of table variables for large data sets – but for very small data sets, table variables can be very useful. I particularly like them for use with the OUTPUT clause of the MERGE statement when trying to log row counts in SSIS.
Next up: Views.