T-SQL programmers commonly ask, "What's the difference between temporary tables
and table variables? How about table expressions such as derived tables, common
table expressions (CTEs), views, and inline functions? When is it appropriate
to use those and not temporary tables or table variables?" Unfortunately, there's
a lot of confusion concerning these temporary objects and constructs, as well
as some false myths.
I've heard people say that table variables have no physical representation, that
they're just memory-resident, whereas temporary tables have physical representation
in the tempdb database—one false myth. Some people believe that table expressions (e.g., derived tables, CTEs) are always physically materialized—another false
myth. Unfortunately, these myths are so common that it's inevitable that programmers listening to such advice will end up writing poor performing code in their
production systems.
So, let's try to uncover the truth about temporary objects and debunk those
myths. I'll start by giving you a general overview of each temporary object
type (i.e., local temporary table, table variable, table expression), then provide
some general recommendations for when to use each. Next month, I'll provide
more concrete and tangible examples—analyzing specific cases, queries,
and execution plans—to make everything more concrete.
Local Temporary Tables
Local temporary tables (name prefixed with #) are created in the tempdb database—specifically,
they have physical representation.
CREATE TABLE #T(col1 INT, …);
As with permanent tables, if the pages of a temporary table are accessed frequently
by SQL Server, they have the potential to remain in cache and permit access there. A
local temporary table is owned by the session that created it and is visible only to that
session. If your code creates it in an ad-hoc batch, the temporary table is automatically
destroyed when the creating session is terminated. It's visible as of the creation point
throughout the session—namely, it's visible to stored procedures and triggers that the
session invokes. If your code creates a local temporary table within a stored procedure
or a trigger, it's automatically destroyed when the creating routine terminates, not
when the session disconnects. In such a case, inner levels in the call stack will have
access to the temporary table but not to outer levels.
SQL Server creates and maintains distribution statistics (histograms) on the
data stored in temporary tables. SQL Server's optimizer will consult those distribution
statistics when, for example, it needs to estimate selectivity of filters to
figure out whether to use an index and in what manner.
You can apply data definition language (DDL) changes to a temporary
table after you create the table (e.g.,
create an index). Note that some
activities against temporary tables might
cause recompilations of stored procedures—for example, data changes,
refresh of statistics, DDL changes. SQL
Server considers any changes you apply
to a local temporary table (including
DDL changes) to be part of an explicit
transaction if one exists. If the transaction rolls back, all changes to the temporary table also roll back.
You can use a SELECT INTO statement to create and populate a temporary table from the resultset of a query.
SELECT INTO is a bulk operation
and is minimally logged by SQL Server
when the recovery model of the database isn't set to FULL. Because tempdb's
recovery model is SIMPLE and can't be
changed, a SELECT INTO operation
that creates and populates a temporary
table is minimally logged.
I won't discuss global temporary tables (name prefixed with ##) here. You use
them in very special cases, and they're irrelevant to a discussion concerning
the choice among temporary tables, table variables, and table expressions.
Table Variables
Table variables are declared like other local variables, with the DECLARE statement:
DECLARE @T TABLE(col1 INT, …);
It's important to understand that the common myth that table variables have
no physical representation in the tempdb database is false. They do have physical
representation in tempdb, much like local temporary tables. As proof, you can
run the code in Listing 1 and see for yourself.
Figure 1 shows the output I received
when I ran this code. You can see that no temporary table existed in tempdb
before the table variable was declared by the first batch of code, and one did
exist after it was declared by the second batch of code. Also, the myth that
there's no difference between temporary tables and table variables is false.
In fact, as I'll describe shortly, there are many differences.
Prev. page
[1]
2
next page 
