Saturday, January 5, 2008

SQL 101 : Temporary Tables

CREATE TABLE #Table1 (ID INT, Name VARCHAR(50) )

There are 2 types of temporary table. Both are stored in the tempdb database.

Local Temporary Tables (prefix #)
Available to the current session (connection per user) only.
Deleted when the session ends (user disconnects).

Global Temporary Tables (prefix ##)
Available to the all connected sessions, after creation.
Deleted when all sessions that used the temp table disconnect.

Global Temp tables are especially useful when dealing with dynamic sql to pass data between sql sessions e.g. sp_executesql results sets.

No comments: