SQL SERVER – How to Create Table Variable and Temporary Table?

A Temporary table uses CREATE TABLE, while a table variable uses DECLARE. I use one row in each beginner example.

CREATE TABLE #TempTable (ID int IDENTITY(1,1));
INSERT #TempTable DEFAULT VALUES;
SELECT ID FROM #TempTable;
DROP TABLE #TempTable;

DECLARE @TableVariable TABLE (ID int);
INSERT @TableVariable VALUES (1);
SELECT ID FROM @TableVariable;

A local #temporary table belongs to its session. Later batches can query it while it remains in scope. Closing the connection or explicitly dropping it removes it. A temporary table created inside a procedure normally ends with that procedure.

A table variable belongs to its batch, procedure or function. Run DECLARE, INSERT and SELECT together. GO ends the batch in SSMS. A later batch can’t reference the earlier @TableVariable.

Both ordinary temporary tables and table variables use tempdb resources. Their statistics, indexing and optimization behavior differ. Newer releases have changed some table-variable compilation behavior. I choose based on the workload rather than the word variable.

Related reading

• SQL SERVER – Difference TempTable and Table Variable – TempTable in Memory a Myth

Temporary storage is not automatically memory-only storage, it is a choice whose scope and optimization behavior matter.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.