Temporary Tables & Table Variables
7 questions found
What is a local temporary table in SQL Server, and how is it different from a regular permanent table?
Beginner
A local temporary table, created with a name starting with a single pound sign, is automatically dropped when the session that created it ends, and is only visible to that specific session, making it useful for storing intermediate results during a complex process without permanently affecting the database's schema.
CREATE TABLE #TempOrders (
OrderId INT,
Amount DECIMAL(10,2)
);
INSERT INTO #TempOrders SELECT OrderId, Amount FROM Orders WHERE Status = 'Pending';
Real-world example
A complex reporting stored procedure stores an intermediate filtered set of pending orders in a local temporary table, using it for several subsequent calculations before the temporary table is automatically cleaned up when the procedure finishes.
Common follow-ups: What happens to a temporary table if the session that created it disconnects unexpectedly?;Can a temporary table have indexes created on it?
Common Table Expressions (CTEs);Stored Procedures & Functions
What is a table variable in SQL Server, and how is it declared and used?
Beginner
A table variable is declared using the DECLARE statement with a table data type, similar to declaring any other variable, and behaves somewhat like a temporary table but with a more limited scope tied strictly to the batch or procedure in which it was declared, and it typically does not support the same range of indexing options as a temporary table.
DECLARE @TempOrders TABLE (
OrderId INT,
Amount DECIMAL(10,2)
);
INSERT INTO @TempOrders SELECT OrderId, Amount FROM Orders WHERE Status = 'Pending';
Real-world example
A stored procedure uses a table variable to hold a small set of intermediate results needed only within that single procedure call, choosing it over a temporary table for its simpler scope and slightly reduced overhead for small data sets.
Common follow-ups: What is the scope difference between a table variable and a local temporary table?;Do table variables support foreign key constraints?
Stored Procedures & Functions;Data Types & Schema Design
What is the difference between a local temporary table and a global temporary table?
Intermediate
A local temporary table, prefixed with a single pound sign, is only visible to the session that created it, while a global temporary table, prefixed with two pound signs, is visible to all sessions connected to the server, and only gets dropped once every session referencing it has disconnected, making it useful for sharing temporary data across multiple connections.
CREATE TABLE ##SharedTempData (
Id INT,
Value VARCHAR(100)
);
-- This table is visible to any session until all referencing sessions disconnect
Real-world example
A batch processing system uses a global temporary table to share intermediate calculation results across several separate connections working together on the same overall task, something a local temporary table could not support.
Common follow-ups: What are the risks of using a global temporary table in a busy, multi user environment?;How do you know when a global temporary table will actually be dropped?
Isolation & Locking;Data Types & Schema Design
How do statistics and query optimization differ between temporary tables and table variables, and how does this affect performance for larger data sets?
Intermediate
SQL Server maintains statistics for temporary tables, similar to regular tables, allowing the optimizer to make reasonably accurate estimates about the amount of data they contain, while table variables historically have had very limited or no statistics, causing the optimizer to often assume they contain only a single row, which can lead to poor execution plans when a table variable actually holds a large amount of data.
-- Temporary table: benefits from statistics for larger data sets
CREATE TABLE #LargeTempData (Id INT, Value VARCHAR(100));
-- Table variable: may perform worse for large amounts of data
DECLARE @LargeTableVar TABLE (Id INT, Value VARCHAR(100));
Real-world example
A developer notices a stored procedure using a table variable performing poorly once it started holding tens of thousands of rows, and switches to a temporary table instead, letting the optimizer generate a much more efficient execution plan based on accurate statistics.
Common follow-ups: At what data size does this statistics difference typically start to matter in practice?;Have recent versions of SQL Server improved statistics support for table variables?
Query Optimization & Plans;Data Types & Schema Design
How would you decide between using a temporary table, a table variable, and a common table expression for a specific intermediate data processing need within a stored procedure?
Advanced
You would choose a table variable for very small, simple intermediate result sets used briefly within a single batch, a temporary table when you expect a larger amount of data, need indexes, or want to reuse the intermediate result across multiple separate statements, and a common table expression when the intermediate logic is only needed once, immediately within a single following statement, and does not need to persist or be indexed separately.
-- Small, simple case: table variable
DECLARE @Ids TABLE (Id INT);
-- Larger, reused, needs indexing: temporary table
CREATE TABLE #LargeResults (Id INT INDEX IX_Id);
-- Used once, immediately: CTE
WITH FilteredData AS (SELECT * FROM Orders WHERE Amount > 100)
SELECT * FROM FilteredData;
Real-world example
A developer building a complex reporting procedure carefully chooses a temporary table for a large intermediate result set that needs to be joined multiple times, while using a simple CTE for a smaller piece of logic only needed once immediately afterward.
Common follow-ups: What specific factors should weigh most heavily in this decision for a given scenario?;Can these three approaches be mixed together within the same stored procedure?
Common Table Expressions (CTEs);Query Optimization & Plans
How does tempdb contention affect the performance of applications that heavily rely on temporary tables, and what strategies help mitigate this?
Advanced
Since all temporary tables and table variables across an entire SQL Server instance are stored in the shared tempdb system database, a busy server creating and dropping many temporary tables can experience contention specifically around tempdb's internal system pages, which can be mitigated by properly configuring multiple tempdb data files and minimizing unnecessary temporary table creation in extremely high frequency code paths.
-- Properly sized and multiple tempdb files help reduce contention
-- from heavy temporary table usage across many concurrent sessions
ALTER DATABASE tempdb ADD FILE (NAME = tempdev2, FILENAME = 'D:\tempdb2.mdf', SIZE = 512MB);
Real-world example
A high traffic application experiencing tempdb related contention during peak hours resolves much of the issue by properly configuring multiple equally sized tempdb data files, following Microsoft's recommended best practices for a busy server.
Common follow-ups: How do you identify tempdb contention as the actual root cause of a performance problem?;What other tempdb best practices help reduce this kind of contention?
SQL Server Architecture & Editions;Query Optimization & Plans
Can you create indexes on a temporary table, and how does this compare to indexing options available for table variables?
Intermediate
Yes, you can create additional indexes on a local or global temporary table after creating it, just like a regular permanent table, giving you the ability to optimize queries against larger intermediate data sets, while table variables in most versions have historically been much more limited in this regard, typically only supporting a primary key or unique constraint defined inline during declaration.
CREATE TABLE #TempOrders (OrderId INT, CustomerId INT, Amount DECIMAL(10,2));
CREATE INDEX IX_TempOrders_CustomerId ON #TempOrders(CustomerId);
Real-world example
A complex reporting procedure creates an additional index on a temporary table holding a large intermediate result set, significantly speeding up subsequent queries that filter and join against that temporary data by customer.
Common follow-ups: Does creating an index on a temporary table add meaningful overhead for very small data sets?;What indexing options, if any, are available directly within a table variable's declaration?
Indexes;Query Optimization & Plans