Temporary tables and table variables

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Use session-scoped storage for a short list of product IDs. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

DECLARE @Selected TABLE (ProductID int NOT NULL PRIMARY KEY);
INSERT INTO @Selected (ProductID) VALUES (1), (2);
SELECT p.ProductID, p.ProductName
FROM dbo.Products AS p JOIN @Selected AS s ON s.ProductID = p.ProductID
ORDER BY p.ProductID;

Read it step by step

1. @Selected is a table variable scoped to this batch.
2. Its primary key makes duplicate selected IDs invalid.
3. The join returns canonical products without creating a permanent table. # temporary tables are a separate option with different statistics and scope.

Check the result

No canonical table is changed.

Practice

Use a # temp table containing ProductIDs 3 and 4, join to Products, and drop it.

CREATE TABLE #Selected (ProductID int NOT NULL PRIMARY KEY);
INSERT INTO #Selected (ProductID) VALUES (3), (4);
SELECT p.ProductID, p.ProductName
FROM dbo.Products AS p JOIN #Selected AS s ON s.ProductID = p.ProductID
ORDER BY p.ProductID;
DROP TABLE #Selected;