Temporary tables and table variables
Temporary tables hold intermediate results while a script or procedure runs. This lesson compares local temp tables, table variables and CTEs, shows when each one fits, and covers table-valued parameters for sending a whole list from C# in one call.
- #Temp tabletempdb, statistics, indexes, session scope
- @Table variableBatch scope, light, kept after ROLLBACK
- CTENamed subquery for one statement
- Table-valued parameterA list sent from C# in one call
Three ways to hold rows for a moment
- #Temp table: a real table in tempdb, visible to your session (and procedures it calls). Has statistics and can have indexes. Dropped when the session or procedure ends.
- @Table variable: lives in the batch or procedure. Lighter, but for years had no statistics (assumed one row); SQL Server 2019 improved this with deferred compilation.
- CTE: not stored at all. It is a named subquery for one statement and is re-evaluated each time it is referenced.
Example: a temp table for a multi-step report
SELECT CustomerId, SUM(Total) AS Spent
INTO #TopCustomers
FROM Orders
WHERE OrderedAt >= DATEADD(month, -12, SYSUTCDATETIME())
GROUP BY CustomerId
HAVING SUM(Total) > 50000;
CREATE CLUSTERED INDEX IX_Top ON #TopCustomers (CustomerId);
SELECT c.Name, t.Spent, COUNT(r.ReviewId) AS Reviews
FROM #TopCustomers t
JOIN Customers c ON c.CustomerId = t.CustomerId
LEFT JOIN Reviews r ON r.CustomerId = t.CustomerId
GROUP BY c.Name, t.Spent;
DROP TABLE IF EXISTS #TopCustomers;Which one to choose
- Many rows, used several times, or joined to big tables: #temp table, because the optimizer gets real statistics.
- A handful of rows, or you need the data to survive a ROLLBACK (table variables are not rolled back): @table variable.
- Breaking one statement into readable steps: CTE.
- ##Global temp tables are visible to every session; avoid them in app code.
Sending a list from C#: table-valued parameters
Instead of a long IN list or one call per row, define a table type and pass a DataTable as one parameter.
// SQL: CREATE TYPE dbo.IdList AS TABLE (Id int PRIMARY KEY);
// SELECT p.* FROM Products p JOIN @Ids i ON i.Id = p.ProductId;
var table = new DataTable();
table.Columns.Add("Id", typeof(int));
foreach (var id in productIds) table.Rows.Add(id);
var p = cmd.Parameters.AddWithValue("@Ids", table);
p.SqlDbType = SqlDbType.Structured;
p.TypeName = "dbo.IdList";Interview question
What is the difference between a temp table and a table variable?
A #temp table is a real table in tempdb with statistics, indexes and session scope, so the optimizer can plan well for many rows. A @table variable is scoped to the batch, historically had no statistics (estimated one row), is not affected by ROLLBACK and causes fewer recompiles. Use temp tables for larger sets and table variables for small ones.
Want the full details? Read the official Microsoft documentation for this topic.