I have been asked what is the difference between Temp Tables, Table Variables, CTEs, and Derived tables. So I thought I would take this opportunity to do a short blog post on each and then summarize the differences.
You can check out my previous T-Shirt Series post What is so Special about a “Temp” table.
Today we will be talking about the commonly misunderstood table variable.
Creation
A Table variable can be created in your queries or procedures by declaring a variable as a table and defining it as such…
DECLARE @MyTempTable table ( Columns, Contstraints …)
Storage Location
A common misconception is that table variables are stored in memory. Actually, they are stored in tempdb as physical structures.
Scope
Table variables are only available within the batch, procedure, or function in which they are declared. As an example, you can create a table variable in one procedure, but you can NOT reference or access it in child calls since it was not declared there.
You can declare and reference table variables in stored procedures as well as scalar or table valued functions.
Note: you can pass the table variable into a child procedure or function as a table valued parameter as outlined here. From C# you can pass a data table to a procedures table valued parameter like this.
Transactions
Table variables do NOT participate in user transactions and as such are NOT subject to rollbacks. This is important to be aware of if you are performing processes that rely on transactions as this can cause many troubleshooting nightmares if you were not aware of it.
Example:
CREATE PROCEDURE dbo.ExampleTransactionWithTempTables
AS
SET NOCOUNT ON
BEGIN
DECLARE @MyTempTable table (ID INT IDENTITY(1,1) PRIMARY KEY)
CREATE TABLE #MyTempTable (ID INT IDENTITY(1,1) PRIMARY KEY)
–start a transaction
BEGIN TRANSACTION
–add a record to our table variable
INSERT INTO @MyTempTable DEFAULT VALUES
INSERT INTO #MyTempTable DEFAULT VALUES
–rollback the transaction
ROLLBACK TRANSACTION
–return the data in the table variable
SELECT ‘@MyTempTable’ as tbl, * FROM @MyTempTable
SELECT ‘#MyTempTable’ as tbl, * FROM #MyTempTable
END
GO
–execute the procedure to see the table variable had data in it, but the temp table doesn’t
EXECUTE dbo.ExampleTransactionWithTempTables
Cardinality
SQL server does not maintain statistics for Table variables, and they are typically estimated to have a single row in them. Remember. when the query optimizer runs over the statement there are typically no rows in the table.
This can cause significant performance problems in certain situations and you should test based on a representative amount of data. I have seen posts describe a rule of thumb as 100,000 cells of data. But I have seen table variables work fine with much larger sets and horribly with much smaller sets.
You may be able to improve the cardinality estimate by performing an explicit recompile on the statement, but you may want to look at using a #Temp table unless there is a specific need for the table variable if you run into performance issues.
Indexes
You cannot create indexes on table variables beyond inline constraints such as primary key or uniqueness constraints. Obviously that gives us a big work around in that if you include your primary key in a uniqueness constraint you have effectively created an index.
DECLARE @MyTempTable table (ID INT IDENTITY(1,1),
SomeValueToIndex varchar(10),
PRIMARY KEY (ID),
UNIQUE NONCLUSTERED ( SomeValueToIndex, ID ))
Note: Starting in SQL 2014 you can add non-clustered indexes to table variables inline without the workaround, as well as many other changes for table variables.
Logging
Statements impacting a table variable are logged in the tempdb transaction log.
So if I were a table variable and was out looking for a t-shirt that described me it would say something like …
Do you have any questions, or tips and tricks for using table variables?
Let’s discuss in the comments below.
