SQL Server Table Variables

In SQL Server, a Table Variable is a special data type used to store a result set for temporary processing. Unlike local variables that hold a single value (like an INT or VARCHAR), a table variable allows you to define a full schema—including columns, data types, and constraints—and perform standard DML operations like INSERT, UPDATE, and DELETE.

Core Syntax and Lifecycle

Table variables are defined using the DECLARE statement and are prefixed with a single @ symbol. Their scope is strictly limited to the batch, stored procedure, or function in which they are declared. Once the batch finishes, the table variable is automatically cleaned up.

Example:

-- 1. Declaration
DECLARE @ProductWorklist TABLE (
    ProductID INT PRIMARY KEY,
    ProductName NVARCHAR(100),
    NewPrice MONEY
);

-- 2. Insertion
INSERT INTO @ProductWorklist (ProductID, ProductName, NewPrice)
SELECT ID, Name, Price * 1.1 
FROM Products 
WHERE CategoryID = 5;

-- 3. Usage
SELECT * FROM @ProductWorklist WHERE NewPrice > 50.00;

Key Characteristics

• Scope: The variable exists only within the current batch. Once the execution finishes (or hits a GO command), the table is automatically dropped from memory.
• Transaction Behavior: Table variables are not affected by ROLLBACK. If you insert data into a table variable and then roll back the transaction, the data in the table variable remains.
• Storage: While often described as “in-memory,” SQL Server may move table variables to tempdb if they become too large.
• Constraints: You can define PRIMARY KEY, UNIQUE constraints, and CHECK constraints, but you cannot create named indexes on them after declaration (though unique constraints create indexes automatically).

Passing Tables to Stored Procedures

You cannot pass a table variable directly as a parameter unless you first create a User-Defined Table Type. This is known as a Table-Valued Parameter (TVP).

Example:

-- Step 1: Create the Type
CREATE TYPE OrderListType AS TABLE (
    OrderID INT,
    Qty INT
);
GO

-- Step 2: Use it in a Procedure (Must be READONLY)
CREATE PROCEDURE ProcessOrders
    @List OrderListType READONLY
AS
BEGIN
    SELECT * FROM @List;
END;

DECLARE @PendingOrders TABLE (RowID INT IDENTITY(1,1), OrderID INT);

INSERT INTO @PendingOrders (OrderID)
SELECT OrderID FROM Orders WHERE Shipped = 0;

DECLARE @CurrentRow INT = 1;
DECLARE @TotalRows INT = (SELECT COUNT(*) FROM @PendingOrders);
DECLARE @ActiveOrderID INT;

WHILE @CurrentRow <= @TotalRows
BEGIN
    SELECT @ActiveOrderID = OrderID FROM @PendingOrders WHERE RowID = @CurrentRow;
    
    -- Perform complex logic for each order
    EXEC ProcessOrder @ActiveOrderID;

    SET @CurrentRow = @CurrentRow + 1;
END

DECLARE @ProductCache TABLE (
    PID INT PRIMARY KEY,         -- Creates a Clustered Index
    SKU NVARCHAR(50) UNIQUE      -- Creates a Non-Clustered Index
);

SELECT * FROM Employees e
JOIN @TableVar t ON e.ID = t.ID
OPTION (RECOMPILE); -- Forces the engine to see actual row count