T-SQL – Temporary tables and table variables

Temporary tables can be used as helper tables. They are stored in tempdb database.
Global temporary tables start with ## and are visible in all sessions.
Local temporary tables start with # and are visible only in current session.

Table variables are similar to temporary tables, but they are not physically stored and should be used only for small amount of data and easy queries. They are visible in current batch only.
Temporary tables play normally with transactions and table variables not. Table variables are not rollbacked when transaction is rollbacked. It can be usefull to reproducing errors.

Creating examples:

CREATE TABLE #MyLocalTempTable -- table visible only in current session
(
	id INT NOT NULL PRIMARY KEY, 
	name NVARCHAR(100) NOT NULL
);

INSERT INTO #MyLocalTempTable VALUES(1, 'Bob');

CREATE TABLE ##MyGlobalTempTable -- table visible in all sessions
(
	id INT NOT NULL PRIMARY KEY, 
	name NVARCHAR(100) NOT NULL
);
INSERT INTO ##MyGlobalTempTable VALUES(1, 'Bob');

DECLARE @MyTableVariable AS TABLE -- variable visible only in current batch
(
	id INT NOT NULL PRIMARY KEY, 
	name NVARCHAR(100) NOT NULL
);
INSERT INTO @MyTableVariable VALUES(1, 'Bob');

Listing temporary objects:

SELECT name FROM tempdb.sys.objects WHERE name LIKE '#%';
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s