Microsoft® SQL Server® 2012 Bible

408

Part IV: Programming with T-SQL

Table variables are declared as variables, rather than created with SQL DDL statements.

When a table variable is referenced with a SQL query, the table is used as a normal

table but named as a variable. The following script must be executed as a single batch or

it will fail:

DECLARE @WorkTable TABLE (

PK INT PRIMARY KEY,

Col1 INT NOT NULL);

INSERT INTO @WorkTable (PK, Col1)

VALUES ( 1, 101);

SELECT PK, Col1

FROM @WorkTable;

Result:

PK Col1

----------- -----------

101

Memory versus Disk; Temp Tables versus Table

Variables

A common SQL myth is that table variables are stored in memory. They’re not. They exist in tempdb

just like a temporary table. However, the life span of a table variable (as well as that of most temporary

tables) is such that it’s extremely unlikely that it would actually be written to disk. The truth is that the

table variable lives in tempdb pages in memory.

So if the difference isn’t memory versus disk, how do you choose between using a temp table or a

table variable? Size and scope.

Rule of thumb: If the temp space will hold more than approximately 250 rows, then go with a temp

table; otherwise choose a table variable. The reason is because temp tables have the overhead of

statistics, whereas table variables do not. This means that for more data, the temp table’s statistics can

help the Query Optimizer choose the best plan. Of course, you always must consider the overhead

of maintaining the statistics.

Table variables don’t have statistics, so they save on the overhead; but without statistics, the Query

Optimizer always assumes the table variable will result in one row and may therefore choose a poor

plan if the table variable contains a lot of data.

Scope is the other consideration. If the temp space must be visible and updatable by called routines,

then you need to choose a temp table.

c16.indd 408c16.indd 408 7/30/2012 5:38:09 PM7/30/2012 5:38:09 PM

http://www.it-ebooks.info