SQL Variable, Temporary and Global Tables

SQL procedures can create multiple types of table entities – it’s up to you do decide which one is best.

The Simplest: Table Variable

If you just want to capture some information to be used in the body of a stored procedure and the table creation and usage is in a single batch (no “GO” statement between creation and usage), think about a table variable. For example, let’s create a simple table and display the results. You must prefix the table name with single “@” character to denote a table variable.

-- Create table variable.
DECLARE @VoidedZeroDollarItems AS TABLE
(
    [OrderNumber] VARCHAR(30) NOT NULL
)

-- Fill it with some example data.
INSERT INTO @VoidedZeroDollarItems ([OrderNumber])
VALUES ('Order XYZ'), ('Order 123')

-- Display the contents.
SELECT *
FROM @VoidedZeroDollarItems

This works great until you enter a new batch. Then the table goes out of scope and is inaccessible. If you add this code to the bottom of your working SQL…

-- End of batch. Execute previous code.
GO

-- Display the contents (again).
SELECT *
FROM #VoidedZeroDollarItems

It will generate an error when it tries to execute the new SELECT statement: Must declare the table variable “@VoidedZeroDollarItems”. Note that this is not too much of a problem most of the time. Table variables cannot have indexes and they are the fastest table type if you have less than 1000 rows. For bigger tables, use temporary tables.

Batch Agnostic: Local Temporary Table

If you must retain the table contents after a batch has completed, you can use a temporary table. The lifetime of these tables is for the entire sproc. Try running this code. You must prefix the table name with single “#” character to denote a local temporary table.

-- Create table variable.
CREATE TABLE #VoidedZeroDollarItems
(
    [OrderNumber] VARCHAR(30) NOT NULL
)

-- Fill it with some example data.
INSERT INTO #VoidedZeroDollarItems ([OrderNumber])
VALUES ('Order XYZ'), ('Order 123')

-- Display the contents.
SELECT *
FROM #VoidedZeroDollarItems

-- End of batch. Execute previous code.
GO

-- Display the contents (again).
SELECT *
FROM #VoidedZeroDollarItems

The temporary tables are stored in the SQL tempdb database and they can have indexes. It is faster than a table variable if you have more that 1000 rows of data.

If you are using SQL Server Management Studio (SSMS) to create and test temporary tables, be aware that the table will exist as long as you are using the same session (SPID). You must delete the table before you run the table creation or you will get an error: There is already an object named ‘#VoidedZeroDollarItems’ in the database. If this is the case, use this code before the “CREATE TABLE” line.

-- Drop the table if it already exists.
IF OBJECT_ID('tempdb..#VoidedZeroDollarItems') IS NOT NULL
    DROP TABLE #VoidedZeroDollarItems

Sharing is caring: Global Temporary Table

There is yet another type of table you can create where its data is available to other sessions. We will use SSMS as a use case in this example. You must prefix the table name with two characters (“##”) to denote a global temporary table.

-- Drop the table if it already exists.
IF OBJECT_ID('tempdb..##VoidedZeroDollarItems') IS NOT NULL
    DROP TABLE ##VoidedZeroDollarItems

-- Create table variable.
CREATE TABLE ##VoidedZeroDollarItems
(
    [OrderNumber] VARCHAR(30) NOT NULL
)

-- Fill it with some example data.
INSERT INTO ##VoidedZeroDollarItems ([OrderNumber])
VALUES ('Order XYZ'), ('Order 123')

-- Display the contents.
SELECT *
FROM ##VoidedZeroDollarItems

-- End of batch. Execute previous code.
GO

-- Display the contents (again).
SELECT *
FROM ##VoidedZeroDollarItems

On the surface, this code appears to act the same as in the previous section. The difference is that the table is available for other sessions to access. Verify this by opening an additional SSMS query window and execute this code.

-- New session in SSMS.
SELECT *
FROM ##VoidedZeroDollarItems

After executing the SELECT in a different session, the stored table is displayed. The table is visible to all SQL sessions until the session that created the table is closed AND all sessions that are referencing the table are closed also.

Related Posts