Temp Table in SQL Server

Background:

Recently my team started using Microsoft SQL Server 2008 R2 as the RDBMS in one of the project and was lucky to be part of developing two critical functionalities which required extensive database development (writing SPs, functions, queries etc). Because of the nature of the problem, I had to use something like temp table a lot. While doing the actual implementation, I wanted to dig further to make sure the concepts, pros and cons are fully clear, also realized to document my learning for larger audience.

The things I have considered during writing this post are – (1) explanation has to be short, simple and easy to remember (2) structure of information has to be well segmented (3) as much example as possible. Hence created three major sections: concepts (facts, pros/cons, short explanation), best practices and examples (tested code)

CONCEPTS:

1. Table variable:
1. Syntax: DECLARE @tableName TABLE (id INT);
2. Facts:

                                                              i.      Behave like local variables in their scoping rules and will be disposed as soon as it’s out of scope

                                                             ii.      Table variables are stored in ‘TempDB’

                                                           iii.      Available constraints that can be used with table variables

1.      CHECK

2.      PRIMARY KEY

3.      UNIQUE KEY

4.      NULL/NOT NULL

3. Advantages

                                                              i.      Considered as ‘Local/Private’ to the process and has less locking to the resources

                                                             ii.      Transaction rollback doesn’t affect table variable as it exists for shorter span and not stored in persistent database

                                                           iii.      Less logging necessary as not stored in persistent database

                                                          iv.      Table variables result in fewer recompilations of a stored procedure as compared to temporary tables (reference-1)

4. Disadvantages

                                                              i.      Local tables often disposed of before can be investigated

                                                             ii.      Cannot be used in dynamic SQL. In EXECUTE and sp_ExecuteSQL (example-1)

                                                           iii.      Cannot be used in ‘SELECT INTO’ to create them dynamically

                                                          iv.      User defined functions cannot be called from CHECK constraints, DEFAULT values

                                                            v.      Cannot be changed after declaration

                                                          vi.      Cannot be truncated

                                                         vii.      Cannot be dropped. Have to let go out of scope

                                                       viii.      Must be referenced by an alias if needed to join

2. Table valued Parameter:
1. Facts:

                                                              i.      A way to send multiple rows of data to a Stored Procedure or function without creating a temporary table or passing so many parameters

                                                             ii.      ‘Table variable’ or ‘table type’ can be passed as ‘Table Valued Parameter’

                                                           iii.      When table variables are passed as parameters below things happen behind the scene

1.      In the TempDB, table variable is materialized

2.      Reference is passed

2. Advantages:

                                                              i.      Can be used to send client table to server

                                                             ii.      Can be used to pass as Table Valued Parameter to SP or function

3. Disadvantages:

                                                              i.      Table Valued Parameter is passed as Read-Only. No INSERT, UPDATE or DELETE operations are allowed

                                                             ii.      Cannot be used in ‘SELECT INTO’ or ‘INSERT EXEC’ because of read only feature

3. Local Temporary Table:
1. Syntax: Table name starts with #
2. Facts:

                                                              i.      Local Temporary tables are private to the process that created it

                                                             ii.      If the same stored procedure is executed simultaneously by several processes, the Database Engine distinguishes the same tables created by the different processes

                                                           iii.      Local temp tables are stored in ‘TempDB’

                                                          iv.      Local temporary tables are dropped automatically at the end of the current session or procedure (Best Practices-4)

                                                            v.      A session specific identifier is added to the name, to make sure that temp tables from other sessions don’t use the same name [same for Global Temp Table]

                                                          vi.      To allow for the suffix, the table name specified for a local temporary name must be less than 116 characters [same for Global Temp Table]

                                                         vii.      Within a stored procedure, a temporary table cannot be created, dropped, and then recreated a new temporary table with the same name (example-4 & 5) [same for Global Temp Table]

3. Advantages:

                                                              i.      Can be referred within batches or in dynamic SQL (example-2 & 6)

                                                             ii.      Can be referred by any nested stored procedures executed by the stored procedure that created the table (example-3)

                                                           iii.      Reduction of required locks as only user has access to it

                                                          iv.      Less logging necessary

4. Disadvantages:

                                                              i.      Cannot be used in views and cannot associate triggers with them

                                                             ii.      Table name limited by 116 characters

4. Global Temporary table:
1. Syntax: table name starts with ##
2. Facts:

                                                              i.      Global temp tables work almost similar to local temp tables

                                                             ii.      global tables aren’t private to the process that created it

                                                           iii.      Global temp tables are visible to all sessions, until the session created it goes out of scope and referring sessions to the table go out of scope

3. Advantages:

                                                              i.      Reduction of required locks as only user has access to it

                                                             ii.      Less logging necessary

4. Disadvantages:

                                                              i.      Usage is very limited

                                                             ii.      Hard to manage data/flow consistency

Notes:

1.      Table variables are completely isolated to the batch that creates them so no 're-resolution' has to occur when a CREATE or ALTER statement takes place, which may occur with a temporary table. Temporary tables need this 're-resolution' so the table can be referenced from a nested stored procedure. Table variables avoid this completely so stored procedures can use plan that is already compiled, thus saving resources to process the stored procedure (References-2)

References:

1. SP recompilation- I have found this link a superb one which explains in detail when a SP is recompiled (cover temp table related recompilation as well)
   URL - http://support.microsoft.com/kb/243586
   
2. http://support.microsoft.com/kb/30597 
3. http://www.sqlservercentral.com/articles/Temporary+Tables/66720/

BEST PRACTICES:

1. Need to put extra caution when using Local Temp Table in the called SP which is defined in calling SP because called SP might throw exception when called from any other places
2. Instead of Global Temp table, try to use permanent table if possible
3. Always use alias for tables (permanent/temp)
4. Don’t need to drop temp tables explicitly at the end of the Stored Procedure/block as SQL Server engine checks and drops any temp tables declared in the scope when the SP execution is ended

CODING:

Example-1: will not compile. Table variable is called in dynamic SQL

-- DROP PROCEDURE SPFirst

CREATE PROCEDURE SPFirst

AS

DECLARE @tableValue TABLE (id INT);

EXECUTE('SELECT * FROM @tableValue');

GO

Example-2: Will work where temp table is used in dynamic SQL

CREATE PROCEDURE SPFirst

AS

CREATE TABLE #tempTable (id INT);

EXECUTE('SELECT * FROM # tempTable’);

GO

Example-3: First SP calls second SP which uses the local temp table created in first. (To test, compile both SPs and execute last line)

-- DROP PROCEDURE SPFirst

CREATE PROCEDURE SPFirst

AS

          CREATE TABLE #tempTable (id INT);

          INSERT INTO #tempTable VALUES(1);

          INSERT INTO #tempTable VALUES(2);

          EXECUTE SPSecond;

GO

-- DROP PROCEDURE SPSecond

CREATE PROCEDURE SPSecond

AS

          SELECT * FROM #tempTable;

GO

EXECUTE SPFirst;

Example-4: Works fine. Global temp table created and dropped from same SP

-- DROP PROCEDURE SPFirst

CREATE PROCEDURE SPFirst

AS

          CREATE TABLE ##tempTable (id INT);

          INSERT INTO ##tempTable VALUES(1);

          INSERT INTO ##tempTable VALUES(2);

         

          DROP TABLE ##tempTable;

GO

Example-5: Will not compile. Temp table (global/local) is created>dropped>recreated with same name. The reason is Temp Table works as local variable in a scope where multiple variable declaration with same name will throw compile time error

CREATE PROCEDURE SPFirst

AS

          CREATE TABLE ##tempTable (id INT);

          INSERT INTO ##tempTable VALUES(1);

          INSERT INTO ##tempTable VALUES(2);

         

          DROP TABLE ##tempTable;

         

          CREATE TABLE ##tempTable (id INT);

GO

Example-6: Will compile but will fail during execution because temp table is defined in child session which is not available in parent session

-- DROP PROCEDURE SPFirst

CREATE PROCEDURE SPFirst

AS

EXECUTE('CREATE TABLE #tempTable (id INT);INSERT INTO #tempTable VALUES(1);INSERT INTO #tempTable VALUES(2);');

SELECT * FROM #tempTable;

GO

EXECUTE SPFirst;

Summary (Reference-03)

Feature	Table Variables	Temporary Tables
Scope	Current batch	Current session, nested stored procedures. Global: all sessions.
Usage	UDFs, Stored Procedures, Triggers, Batches.	Stored Procedures, Triggers, Batches.
Creation	DECLARE statement only.	CREATE TABLE statement. SELECT INTO statement.
Table name	Maximum 128 characters.	Maximum 116 characters.
Column data types	Can use user-defined data types. Can use XML collections.	User-defined data types and XML collections must be in tempdb to use.
Collation	String columns inherit collation from current database.	String columns inherit collation from tempdb database.
Indexes	Can only have indexes that are automatically created with PRIMARY KEY & UNIQUE constraints as part of the DECLARE statement.	Indexes can be added after the table has been created.
Constraints	PRIMARY KEY, UNIQUE, NULL, CHECK, but they must be incorporated with the creation of the table in the DECLARE statement. FOREIGN KEY not allowed.	PRIMARY KEY, UNIQUE, NULL, CHECK. Can be part of the CREATE TABLE statement, or can be added after the table has been created. FOREIGN KEY not allowed.
Post-creation DDL (indexes, columns)	Statements are not allowed.	Statements are allowed.
Data insertion	INSERT statement (SQL 2000: cannot use INSERT/EXEC).	INSERT statement, including INSERT/EXEC. SELECT INTO statement.
Insert explicit values into identity columns (SET IDENTITY_INSERT).	The SET IDENTITY_INSERT statement is not supported.	The SET IDENTITY_INSERT statement is supported.
Truncate table	Not allowed.	Allowed.
Destruction	Automatically at the end of the batch.	Explicitly with DROP TABLE statement. Automatically when session ends. (Global: also when other sessions have no statements using table.)
Transactions	Last only for length of update against the table variable. Uses less than temporary tables.	Last for the length of the transaction. Uses more than table variables.
Stored procedure recompilations	Not applicable.	Creating temp table and data inserts cause procedure recompilations.
Rollbacks	Not affected (Data not rolled back).	Affected (Data is rolled back).
Statistics	Optimizer cannot create any statistics on columns, so it treats table variable has having 1 record when creating execution plans.	Optimizer can create statistics on columns. Uses actual row count for generation execution plan.
Pass to stored procedures	SQL 2008 only, with predefined user-defined table type.	Not allowed to pass, but they are still in scope to nested procedures.
Explicitly named objects (indexes, constraints).	Not allowed.	Allowed, but be aware of multi-user issues.
Dynamic SQL	Must declare table variable inside the dynamic SQL.	Can use temporary tables created prior to calling the dynamic sql.