This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
sql servert-sqltemporary-tables
This seems to be an area with quite a few myths and conflicting views.
So what is the difference between a table variable and a local temporary table in SQL Server?
This is pretty broad, but I'll give you as general an answer as I can.
CTEs...
VIEW
s#Temp Tables...
As far as when to use each, they have very different use cases. If you will have a very large result set, or need to refer to it more than once, put it in a #temp
table. If it needs to be recursive, is disposable, or is just to simplify something logically, a CTE
is preferred.
Also, a CTE
should never be used for performance. You will almost never speed things up by using a CTE, because, again, it's just a disposable view. You can do some neat things with them but speeding up a query isn't really one of them.
The output of SET STATISTICS IO ON
for both looks similar
SET STATISTICS IO ON;
PRINT 'V2'
EXEC dbo.V2 10
PRINT 'T2'
EXEC dbo.T2 10
Gives
V2
Table '#58B62A60'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3
Table '#58B62A60'. Scan count 10, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3
T2
Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3
Table '#T__ ... __00000000E2FE'. Scan count 0, logical reads 20
Table 'NUM'. Scan count 1, logical reads 3
And as Aaron points out in the comments the plan for the table variable version is actually less efficient as whilst both have a nested loops plan driven by an index seek on dbo.NUM
the #temp
table version performs a seek into the index on [#T].n = [dbo].[NUM].[n]
with residual predicate [#T].[n]<=[@total]
whereas the table variable version performs an index seek on @V.n <= [@total]
with residual predicate @V.[n]=[dbo].[NUM].[n]
and so processes more rows (which is why this plan performs so poorly for larger number of rows)
Using Extended Events to look at the wait types for the specific spid gives these results for 10,000 executions of EXEC dbo.T2 10
+---------------------+------------+----------------+----------------+----------------+
| | | Total | Total Resource | Total Signal |
| Wait Type | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| SOS_SCHEDULER_YIELD | 16 | 19 | 19 | 0 |
| PAGELATCH_SH | 39998 | 14 | 0 | 14 |
| PAGELATCH_EX | 1 | 0 | 0 | 0 |
+---------------------+------------+----------------+----------------+----------------+
and these results for 10,000 executions of EXEC dbo.V2 10
+---------------------+------------+----------------+----------------+----------------+
| | | Total | Total Resource | Total Signal |
| Wait Type | Wait Count | Wait Time (ms) | Wait Time (ms) | Wait Time (ms) |
+---------------------+------------+----------------+----------------+----------------+
| PAGELATCH_EX | 2 | 0 | 0 | 0 |
| PAGELATCH_SH | 1 | 0 | 0 | 0 |
| SOS_SCHEDULER_YIELD | 676 | 0 | 0 | 0 |
+---------------------+------------+----------------+----------------+----------------+
So it is clear that the number of PAGELATCH_SH
waits is much higher in the #temp
table case. I'm not aware of any way of adding the wait resource to the extended events trace so to investigate this further I ran
WHILE 1=1
EXEC dbo.T2 10
Whilst in another connection polling sys.dm_os_waiting_tasks
CREATE TABLE #T(resource_description NVARCHAR(2048))
WHILE 1=1
INSERT INTO #T
SELECT resource_description
FROM sys.dm_os_waiting_tasks
WHERE session_id=<spid_of_other_session> and wait_type='PAGELATCH_SH'
After leaving that running for about 15 seconds it had gathered the following results
+-------+----------------------+
| Count | resource_description |
+-------+----------------------+
| 1098 | 2:1:150 |
| 1689 | 2:1:146 |
+-------+----------------------+
Both of these pages being latched belong to (different) non clustered indexes on the tempdb.sys.sysschobjs
base table named 'nc1'
and 'nc2'
.
Querying tempdb.sys.fn_dblog
during the runs indicates that the number of log records added by the first execution of each stored procedure was somewhat variable but for subsequent executions the number added by each iteration was very consistent and predictable. Once the procedure plans are cached the number of log entries are about half those needed for the #temp
version.
+-----------------+----------------+------------+
| | Table Variable | Temp Table |
+-----------------+----------------+------------+
| First Run | 126 | 72 or 136 |
| Subsequent Runs | 17 | 32 |
+-----------------+----------------+------------+
Looking at the transaction log entries in more detail for the #temp
table version of the SP each subsequent invocation of the stored procedure creates three transactions and the table variable one only two.
+---------------------------------+----+---------------------------------+----+
| #Temp Table | @Table Variable |
+---------------------------------+----+---------------------------------+----+
| CREATE TABLE | 9 | | |
| INSERT | 12 | TVQuery | 12 |
| FCheckAndCleanupCachedTempTable | 11 | FCheckAndCleanupCachedTempTable | 5 |
+---------------------------------+----+---------------------------------+----+
The INSERT
/TVQUERY
transactions are identical except for the name. This contains the log records for each of the 10 rows inserted to the temporary table or table variable plus the LOP_BEGIN_XACT
/ LOP_COMMIT_XACT
entries.
The CREATE TABLE
transaction only appears in the #Temp
version and looks as follows.
+-----------------+-------------------+---------------------+
| Operation | Context | AllocUnitName |
+-----------------+-------------------+---------------------+
| LOP_BEGIN_XACT | LCX_NULL | |
| LOP_SHRINK_NOOP | LCX_NULL | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysschobjs.clst |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1 |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc1 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2 |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc2 |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysschobjs.clst |
| LOP_COMMIT_XACT | LCX_NULL | |
+-----------------+-------------------+---------------------+
The FCheckAndCleanupCachedTempTable
transaction appears in both but has 6 additional entries in the #temp
version. These are the 6 rows referring to sys.sysschobjs
and they have exactly the same pattern as above.
+-----------------+-------------------+----------------------------------------------+
| Operation | Context | AllocUnitName |
+-----------------+-------------------+----------------------------------------------+
| LOP_BEGIN_XACT | LCX_NULL | |
| LOP_DELETE_ROWS | LCX_NONSYS_SPLIT | dbo.#7240F239.PK__#T________3BD0199374293AAB |
| LOP_HOBT_DELTA | LCX_NULL | |
| LOP_HOBT_DELTA | LCX_NULL | |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysschobjs.clst |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc1 |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc1 |
| LOP_DELETE_ROWS | LCX_MARK_AS_GHOST | sys.sysschobjs.nc2 |
| LOP_INSERT_ROWS | LCX_INDEX_LEAF | sys.sysschobjs.nc2 |
| LOP_MODIFY_ROW | LCX_CLUSTERED | sys.sysschobjs.clst |
| LOP_COMMIT_XACT | LCX_NULL | |
+-----------------+-------------------+----------------------------------------------+
Looking at these 6 rows in both transactions they correspond to the same operations. The first LOP_MODIFY_ROW, LCX_CLUSTERED
is an update to the modify_date
column in sys.objects
. The remaining five rows are all concerned with object renaming. Because name
is a key column of both affected NCIs (nc1
and nc2
) this is carried out as a delete/insert for those then it goes back to the clustered index and updates that too.
It appears that for the #temp
table version when the stored procedure ends part of the clean up carried out by the FCheckAndCleanupCachedTempTable
transaction is to rename the temp table from something like #T__________________________________________________________________________________________________________________00000000E316
to a different internal name such as #2F4A0079
and when it is entered the CREATE TABLE
transaction renames it back. This flip flopping name can be seen by in one connection executing dbo.T2
in a loop whilst in another
WHILE 1=1
SELECT name, object_id, create_date, modify_date
FROM tempdb.sys.objects
WHERE name LIKE '#%'
Example Results
So one potential explanation for the observed performance differential as alluded to by Alex is that it is this additional work maintaining the system tables in tempdb
that is responsible.
Running both procedures in a loop the Visual Studio Code profiler reveals the following
+-------------------------------+--------------------+-------+-----------+
| Function | Explanation | Temp | Table Var |
+-------------------------------+--------------------+-------+-----------+
| CXStmtDML::XretExecute | Insert ... Select | 16.93 | 37.31 |
| CXStmtQuery::ErsqExecuteQuery | Select Max | 8.77 | 23.19 |
+-------------------------------+--------------------+-------+-----------+
| Total | | 25.7 | 60.5 |
+-------------------------------+--------------------+-------+-----------+
The table variable version spends about 60% of the time performing the insert statement and the subsequent select whereas the temporary table is less than half that. This is inline with the timings shown in the OP and with the conclusion above that the difference in performance is down to time spent performing ancillary work not due to time spent in the query execution itself.
The most important functions contributing towards the "missing" 75% in the temporary table version are
+------------------------------------+-------------------+
| Function | Inclusive Samples |
+------------------------------------+-------------------+
| CXStmtCreateTableDDL::XretExecute | 26.26% |
| CXStmtDDL::FinishNormalImp | 4.17% |
| TmpObject::Release | 27.77% |
+------------------------------------+-------------------+
| Total | 58.20% |
+------------------------------------+-------------------+
Under both the create and release functions the function CMEDProxyObject::SetName
is shown with an inclusive sample value of 19.6%
. From which I infer that 39.2% of the time in the temporary table case is taken up with the renaming described earlier.
And the largest ones in the table variable version contributing to the other 40% are
+-----------------------------------+-------------------+
| Function | Inclusive Samples |
+-----------------------------------+-------------------+
| CTableCreate::LCreate | 7.41% |
| TmpObject::Release | 12.87% |
+-----------------------------------+-------------------+
| Total | 20.28% |
+-----------------------------------+-------------------+
Best Answer
Contents
Caveat
This answer discusses "classic" table variables introduced in SQL Server 2000. SQL Server 2014 in memory OLTP introduces Memory-Optimized Table Types. Table variable instances of those are different in many respects to the ones discussed below! (more details).
Storage Location
No difference. Both are stored in
tempdb
.I've seen it suggested that for table variables this is not always the case but this can be verified from the below
Example Results (showing location in
tempdb
the 2 rows are stored)Logical Location
@table_variables
behave more as though they were part of the current database than#temp
tables do. For table variables (since 2005) column collations if not specified explicitly will be that of the current database whereas for#temp
tables it will use the default collation oftempdb
(More details). Additionally User-defined data types and XML collections must be in tempdb to use for#temp
tables but table variables can use them from the current database (Source).SQL Server 2012 introduces contained databases. the behavior of temporary tables in these differs (h/t Aaron)
Visibility to different scopes
@table_variables
can only be accessed within the batch and scope in which they are declared.#temp_tables
are accessible within child batches (nested triggers, procedure,exec
calls).#temp_tables
created at the outer scope (@@NESTLEVEL=0
) can span batches too as they persist until the session ends. Neither type of object can be created in a child batch and accessed in the calling scope however as discussed next (global##temp
tables can be though).Lifetime
@table_variables
are created implicitly when a batch containing aDECLARE @.. TABLE
statement is executed (before any user code in that batch runs) and are dropped implicitly at the end.Although the parser will not allow you to try and use the table variable before the
DECLARE
statement the implicit creation can be seen below.#temp_tables
are created explicitly when the TSQLCREATE TABLE
statement is encountered and can be dropped explicitly withDROP TABLE
or will be dropped implicitly when the batch ends (if created in a child batch with@@NESTLEVEL > 0
) or when the session ends otherwise.NB: Within stored routines both types of object can be cached rather than repeatedly creating and dropping new tables. There are restrictions on when this caching can occur however that are possible to violate for
#temp_tables
but which the restrictions on@table_variables
prevent anyway. The maintenance overhead for cached#temp
tables is slightly greater than for table variables as illustrated here.Object Metadata
This is essentially the same for both types of object. It is stored in the system base tables in
tempdb
. It is more straightforward to see for a#temp
table however asOBJECT_ID('tempdb..#T')
can be used to key into the system tables and the internally generated name is more closely correlated with the name defined in theCREATE TABLE
statement. For table variables theobject_id
function does not work and the internal name is entirely system generated with no relationship to the variable name. The below demonstrates the metadata is still there however by keying in on a (hopefully unique) column name. For tables without unique column names the object_id can be determined usingDBCC PAGE
as long as they are not empty.Output
Transactions
Operations on
@table_variables
are carried out as system transactions, independent of any outer user transaction, whereas the equivalent#temp
table operations would be carried out as part of the user transaction itself. For this reason aROLLBACK
command will affect a#temp
table but leave the@table_variable
untouched.Logging
Both generate log records to the
tempdb
transaction log. A common misconception is that this is not the case for table variables so a script demonstrating this is below, it declares a table variable, adds a couple of rows then updates them and deletes them.Because the table variable is created and dropped implicitly at the start and the end of the batch it is necessary to use multiple batches in order to see the full logging.
Returns
###Detailed view
###Summary View (includes logging for implicit drop and system base tables)
As far as I've been able to discern operations on both generate roughly equal amounts of logging.
Whilst the quantity of logging is very similar one important difference is that log records related to
#temp
tables can not be cleared out until any containing user transaction finishes so a long running transaction that at some point writes to#temp
tables will prevent log truncation intempdb
whereas the autonomous transactions spawned for table variables do not.Table variables do not support
TRUNCATE
so can be at a logging disadvantage when the requirement is to remove all rows from a table (though for very small tablesDELETE
can work out better anyway)Cardinality
Many of the execution plans involving table variables will show a single row estimated as the output from them. Inspecting the table variable properties shows that SQL Server believes the table variable has zero rows (Why it estimates 1 row will be emitted from a zero row table is explained by @Paul White here).
However the results shown in the previous section do show an accurate
rows
count insys.partitions
. The issue is that on most occasions the statements referencing table variables are compiled while the table is empty. If the statement is (re)compiled after@table_variable
is populated then this will be used for the table cardinality instead (This might happen due to an explicitrecompile
or perhaps because the statement also references another object that causes a deferred compile or a recompile.)Plan shows accurate estimated row count following deferred compile.
In SQL Server 2012 SP2, trace flag 2453 is introduced. More details are under "Relational Engine" here.
When this trace flag is enabled it can cause automatic recompiles to take account of changed cardinality as discussed further very shortly.
NB: On Azure in compatibility level 150 compilation of the statement is now deferred until first execution. This means that it will no longer be subject to the zero row estimate problem.
No column statistics
Having a more accurate table cardinality doesn't mean the estimated row count will be any more accurate however (unless doing an operation on all rows in the table). SQL Server does not maintain column statistics for table variables at all so will fall back on guesses based upon the comparison predicate (e.g. that 10% of the table will be returned for an
=
against a non unique column or 30% for a>
comparison). In contrast column statistics are maintained for#temp
tables.SQL Server maintains a count of the number of modifications made to each column. If the number of modifications since the plan was compiled exceeds the recompilation threshold (RT) then the plan will be recompiled and statistics updated. The RT depends on table type and size.
From Plan Caching in SQL Server 2008
the
KEEP PLAN
hint can be used to set the RT for#temp
tables the same as for permanent tables.The net effect of all this is that often the execution plans generated for
#temp
tables are orders of magnitudes better than for@table_variables
when many rows are involved as SQL Server has better information to work with.NB1: Table variables do not have statistics but can still incur a "Statistics Changed" recompile event under trace flag 2453 (does not apply for "trivial" plans) This appears to occur under the same recompile thresholds as shown for temp tables above with an additional one that if
N=0 -> RT = 1
. i.e. all statements compiled when the table variable is empty will end up getting a recompile and correctedTableCardinality
the first time they are executed when non empty. The compile time table cardinality is stored in the plan and if the statement is executed again with the same cardinality (either due to flow of control statements or reuse of a cached plan) no recompile occurs.NB2: For cached temporary tables in stored procedures the recompilation story is much more complicated than described above. See Temporary Tables in Stored Procedures for all the gory details.
Recompiles
As well as the modification based recompiles described above
#temp
tables can also be associated with additional compiles simply because they allow operations that are prohibited for table variables that trigger a compile (e.g. DDL changesCREATE INDEX
,ALTER TABLE
)Locking
It has been stated that table variables do not participate in locking. This is not the case. Running the below outputs to the SSMS messages tab the details of locks taken and released for an insert statement.
For queries that
SELECT
from table variables Paul White points out in the comments that these automatically come with an implicitNOLOCK
hint. This is shown below###Output
The impact of this on locking might be quite minor however.
Neither of these return results in index key order indicating that SQL Server used an allocation ordered scan for both.
I ran the above script twice and the results for the second run are below
The locking output for the table variable is indeed extremely minimal as SQL Server just acquires a schema stability lock on the object. But for a
#temp
table it is nearly as light in that it takes out an object levelS
lock. ANOLOCK
hint orREAD UNCOMMITTED
isolation level can of course be specified explicitly when working with#temp
tables as well.Similarly to the issue with logging a surrounding user transaction can mean that the locks are held longer for
#temp
tables. With the script belowwhen run outside of an explicit user transaction for both cases the only lock returned when checking
sys.dm_tran_locks
is a shared lock on theDATABASE
.On uncommenting the
BEGIN TRAN ... ROLLBACK
26 rows are returned showing that locks are held both on the object itself and on system table rows to allow for rollback and prevent other transactions from reading uncommitted data. The equivalent table variable operation is not subject to rollback with the user transaction and has no need to hold these locks for us to check in the next statement but tracing locks acquired and released in Profiler or using trace flag 1200 shows plenty of locking events do still occur.Indexes
For versions prior to SQL Server 2014 indexes can only be created implicitly on table variables as a side effect of adding a unique constraint or primary key. This does of course mean that only unique indexes are supported. A non unique non clustered index on a table with a unique clustered index can be simulated however by simply declaring it
UNIQUE NONCLUSTERED
and adding the CI key to the end of the desired NCI key (SQL Server would do this behind the scenes anyway even if a non unique NCI could be specified)As demonstrated earlier various
index_option
s can be specified in the constraint declaration includingDATA_COMPRESSION
,IGNORE_DUP_KEY
, andFILLFACTOR
(though there is no point in setting that one as it would only make any difference on index rebuild and you can't rebuild indexes on table variables!)Additionally table variables do not support
INCLUDE
d columns, filtered indexes (until 2016) or partitioning,#temp
tables do (the partition scheme must be created intempdb
).Indexes in SQL Server 2014
Non unique indexes can be declared inline in the table variable definition in SQL Server 2014. Example syntax for this is below.
Indexes in SQL Server 2016
From CTP 3.1 it is now possible to declare filtered indexes for table variables. By RTM it may be the case that included columns are also allowed albeit they will likely not make it into SQL16 due to resource constraints
Parallelism
Queries that insert into (or otherwise modify)
@table_variables
cannot have a parallel plan,#temp_tables
are not restricted in this manner.There is an apparent workaround in that rewriting as follows does allow the
SELECT
part to take place in parallel but that ends up using a hidden temporary table (behind the scenes)There is no such limitation in queries that select from table variables as illustrated in my answer here
Other Functional Differences
#temp_tables
cannot be used inside a function.@table_variables
can be used inside scalar or multi-statement table UDFs.@table_variables
cannot have named constraints.@table_variables
cannot beSELECT
-edINTO
,ALTER
-ed,TRUNCATE
d or be the target ofDBCC
commands such asDBCC CHECKIDENT
or ofSET IDENTITY INSERT
and do not support table hints such asWITH (FORCESCAN)
CHECK
constraints on table variables are not considered by the optimizer for simplification, implied predicates or contradiction detection.PAGELATCH_EX
waits. (Example)Memory Only?
As stated at the beginning both get stored on pages in
tempdb
. However I didn't address whether there was any difference in behaviour when it comes to writing these pages to disc.I've done a small amount of testing on this now and so far have seen no such difference. In the specific test I did on my instance of SQL Server 250 pages seems to be the cut off point before the data file gets written to.
Running the below script
And monitoring writes to the
tempdb
data file with Process Monitor I saw none (except occasionally ones to the database boot page at offset 73,728). After changing250
to251
I began to see writes as below.The screenshot above shows 5 * 32 page writes and one single page write indicating that 161 of the pages were written to disc. I got the same cut off point of 250 pages when testing with table variables too. The script below shows it a different way by looking at
sys.dm_os_buffer_descriptors
###Results
Showing that 192 pages were written to disc and the dirty flag cleared. It also shows that being written to disc doesn't mean that pages will be evicted from the buffer pool immediately. The queries against this table variable could still be satisfied entirely from memory.
On an idle server with
max server memory
set to2000 MB
andDBCC MEMORYSTATUS
reporting Buffer Pool Pages Allocated as approx 1,843,000 KB (c. 23,000 pages) I inserted to the tables above in batches of 1,000 rows/pages and for each iteration recorded.Both the table variable and the
#temp
table gave nearly identical graphs and managed to pretty much max out the buffer pool before getting to the point that they weren't entirely held in memory so there doesn't seem to be any particular limitation on how much memory either can consume.