SQL Server Interview Question #172

What is a table variable?

Database Design, Normalization & Advanced SQL Senior Advanced

Detailed Explanation

A table variable is declared using DECLARE @Name TABLE and stores tabular intermediate data, using tempdb infrastructure internally.

Table variables have different optimization and scope characteristics from temporary tables. Modern SQL Server versions have improved table-variable cardinality estimation in some scenarios, but temporary tables can still be preferable for larger or more complex intermediate datasets because of statistics and indexing flexibility.

The choice should be based on workload and measured execution plans rather than the simplistic rule that table variables are always memory-only or always faster.

Code Example

DECLARE @SelectedProducts TABLE
(
    Id INT PRIMARY KEY,
    Name NVARCHAR(200)
);

INSERT INTO @SelectedProducts (Id, Name)
SELECT Id, Name
FROM dbo.Products
WHERE IsActive = 1;

SELECT * FROM @SelectedProducts;