Table Variables in SQL Server

DECLARE @MyTable TABLE (Col1 INT, Col2 VARCHAR(50));

Key points:

  - Scope = the current BATCH only (until the next GO).
  - NOT affected by ROLLBACK (data stays!) -> great for logging inside transactions.
  - No statistics -> optimizer may guess wrong row counts on big data.
  - Indexes only inline: PRIMARY KEY, UNIQUE, or INDEX in the declaration.
  - Can be passed to procedures as Table-Valued Parameters (see file 14).
IMPORTANT: Run each section from "DECLARE" to "GO" together.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Part A: DECLARE, INSERT, SELECT, UPDATE, DELETE

DECLARE @TopProducts TABLE
(
    ProductID   INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Revenue     DECIMAL(14,2),
    UnitsSold   INT
);

INSERT INTO @TopProducts (ProductID, ProductName, Revenue, UnitsSold)
SELECT TOP (5) p.ProductID, p.ProductName, SUM(oi.LineTotal), SUM(oi.Quantity)
FROM dbo.OrderItems oi
JOIN dbo.Products p ON p.ProductID = oi.ProductID
GROUP BY p.ProductID, p.ProductName
ORDER BY SUM(oi.LineTotal) DESC;

SELECT * FROM @TopProducts ORDER BY Revenue DESC;

-- Update / delete work like a normal table
UPDATE @TopProducts SET ProductName = ProductName + ' *' WHERE UnitsSold > 20;
DELETE FROM @TopProducts WHERE Revenue < 1000000;

SELECT * FROM @TopProducts;
GO
-- After GO, @TopProducts no longer exists. This would fail:
-- SELECT * FROM @TopProducts;

(5 rows affected)

(5 rows affected)
(1 row affected)

Part B: Table Variable with inline indexes and defaults

DECLARE @Tasks TABLE
(
    TaskID      INT IDENTITY(1,1) PRIMARY KEY,
    TaskName    VARCHAR(50) NOT NULL,
    Priority    TINYINT     NOT NULL DEFAULT 3,
    AssignedTo  INT         NULL,
    IsDone      BIT         NOT NULL DEFAULT 0,
    UNIQUE (TaskName),
    INDEX IX_Priority NONCLUSTERED (Priority)
);

INSERT INTO @Tasks (TaskName, Priority, AssignedTo) VALUES
('Monthly sales report', 1, 14),
('Backup database',      1, 15),
('Fix login bug',        2, 12),
('Update price list',    3, NULL),
('Clean customer data',  2, 14);

SELECT t.TaskName, t.Priority, ISNULL(e.FirstName, '-- unassigned --') AS AssignedTo
FROM @Tasks t
LEFT JOIN dbo.Employees e ON e.EmployeeID = t.AssignedTo
ORDER BY t.Priority, t.TaskName;
GO

(5 rows affected)

Part C: Table Variable vs Temp Table inside a ROLLBACK

This is the BIGGEST practical difference.

DROP TABLE IF EXISTS #TempLog;
CREATE TABLE #TempLog (Msg VARCHAR(100));
DECLARE @VarLog TABLE (Msg VARCHAR(100));

BEGIN TRAN;
    INSERT INTO #TempLog VALUES ('Written inside transaction');
    INSERT INTO @VarLog  VALUES ('Written inside transaction');
ROLLBACK TRAN;

SELECT 'Temp table'     AS Type, COUNT(*) AS RowsAfterRollback FROM #TempLog   -- 0 rows
UNION ALL
SELECT 'Table variable',          COUNT(*)                       FROM @VarLog;  -- 1 row survives!

DROP TABLE #TempLog;
GO

(1 row affected)
(1 row affected)

Part D: Practical use - keep an audit log even when the transaction fails

DECLARE @AuditLog TABLE (StepNo INT, Info VARCHAR(200), LoggedAt DATETIME2 DEFAULT SYSDATETIME());

BEGIN TRY
    BEGIN TRAN;
        INSERT INTO @AuditLog (StepNo, Info) VALUES (1, 'Started price update');

        UPDATE dbo.Products SET UnitPrice = UnitPrice * 1.05 WHERE CategoryID = 10;
        INSERT INTO @AuditLog (StepNo, Info) VALUES (2, CONCAT(@@ROWCOUNT, ' stationery prices updated'));

        -- Simulate an error : divide by zero
        DECLARE @x INT = 1 / 0;

        INSERT INTO @AuditLog (StepNo, Info) VALUES (3, 'Finished');   -- never reached
    COMMIT TRAN;
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0 ROLLBACK TRAN;      -- price update is undone
    INSERT INTO @AuditLog (StepNo, Info) VALUES (99, 'ERROR: ' + ERROR_MESSAGE());
END CATCH;

SELECT * FROM @AuditLog;   -- log rows written BEFORE the rollback are still here
GO

(1 row affected)
(3 rows affected)
(1 row affected)
(1 row affected)

Part E: Loop through rows with a table variable (instead of a cursor)

DECLARE @Depts TABLE (RowNo INT IDENTITY(1,1) PRIMARY KEY, DepartmentID INT, DepartmentName VARCHAR(50));
INSERT INTO @Depts (DepartmentID, DepartmentName)
SELECT DepartmentID, DepartmentName FROM dbo.Departments ORDER BY DepartmentID;

DECLARE @i INT = 1, @max INT = (SELECT COUNT(*) FROM @Depts);
DECLARE @DeptID INT, @DeptName VARCHAR(50), @Cnt INT;

WHILE @i <= @max
BEGIN
    SELECT @DeptID = DepartmentID, @DeptName = DepartmentName FROM @Depts WHERE RowNo = @i;
    SELECT @Cnt = COUNT(*) FROM dbo.Employees WHERE DepartmentID = @DeptID;

    PRINT CONCAT(@DeptName, ' has ', @Cnt, ' employee(s)');   -- see the Messages tab
    SET @i += 1;
END;
GO

(8 rows affected)
Executive has 1 employee(s)
Sales has 6 employee(s)
Marketing has 3 employee(s)
IT has 7 employee(s)
HR has 2 employee(s)
Finance has 3 employee(s)
Operations has 2 employee(s)
Research has 0 employee(s)

Part F: Table variable used with OUTPUT clause (capture changed rows)

DECLARE @Changed TABLE (ProductID INT, OldPrice DECIMAL(10,2), NewPrice DECIMAL(10,2));

BEGIN TRAN;
    UPDATE dbo.Products
    SET UnitPrice = UnitPrice + 100
    OUTPUT inserted.ProductID, deleted.UnitPrice, inserted.UnitPrice INTO @Changed
    WHERE CategoryID = 6;
ROLLBACK TRAN;

SELECT * FROM @Changed;   -- still has rows, even after ROLLBACK
GO

(5 rows affected)

Practice Questions

1. Declare @CityCount table, fill it with number of customers per city, show cities with > 1 customer.
2. Use a table variable to store all orders of customer 1, then compute their total value.
3. Show that a table variable cannot be used after GO (read the error).
4. Write a WHILE loop over @Months (Jan-Jun 2025) and PRINT revenue for each month.