407
Chapter 16: Programming with T-SQL
16
Global Temporary Tables
Global temporary tables are similar to local temporary tables but they have a broader scope.
All users can reference a global temporary table, and the life of the table extends until the
session that created the table disconnects.To create a global temporary table, begin the table name with two pound signs, for exam-
ple, ##TableName. The following code sample tests to determine whether the global tempo-
rary table exists and creates one if it doesn’t:IF NOT EXISTS(
SELECT * FROM tempdb.sys.objects
WHERE name = '##TempWork')
CREATE TABLE ##TempWork(
PK INT PRIMARY KEY,
Col1 INT
);There are a few benefi ts of global temporary tables and many drawbacks. Application code that depends on the exis-
tence of a global temporary table can get tripped up if it doesn’t exist or tries to create one that already exists. If you cre-
ate global temporary tables, consider whether that data would be better stored in a permanent table in your database.When a temporary table is required, it’s likely used for a work in progress. Another alternative
is to simply create a standard user table in tempdb. Every time the SQL Server is restarted, it
dumps and rebuilds tempdb, effectively clearing the alternative temporary worktable.Table Variables
Table variables are similar to temporary tables. The main difference, besides syntax, is that
table variables have the same scope and life as a local variable. They are seen only by the
batch, procedure, or function that creates them. To be seen by called stored procedures, the
table variables must be passed in as table-valued parameters, and then they are read-only
in the called routine.The life span of a table variable is also much shorter than a temp table. Table variables
cease to exist when the batch, procedure, or function concludes. Table variables have a few
additional limitations:■ (^) Table variables may not be created by means of the select * into or
insert into @tablename exec table syntax.
■ (^) Table variables are limited in their allowable constraints: No foreign keys are allowed.
Primary keys, defaults, nulls, check constraints, and unique constraints are OK.
■ (^) Table variables may not have any dependent objects, such as triggers or foreign keys.
c16.indd 407c16.indd 407 7/30/2012 5:38:09 PM7/30/2012 5:38:09 PM
http://www.it-ebooks.info