SQL Server Interview Question #171

What is a temporary table?

Database Design, Normalization & Advanced SQL Senior Advanced

Detailed Explanation

A temporary table is a table stored in tempdb and intended for temporary processing. A local temporary table begins with # and is scoped primarily to the creating session, while a global temporary table begins with ## and has broader visibility subject to SQL Server lifetime rules.

Temporary tables support indexes and statistics and are often useful when intermediate results need to be reused or when breaking a complex operation into stages improves performance.

Code Example

CREATE TABLE #ProductTotals
(
    CategoryId INT PRIMARY KEY,
    ProductCount INT NOT NULL
);

INSERT INTO #ProductTotals
SELECT CategoryId, COUNT(*)
FROM dbo.Products
GROUP BY CategoryId;

SELECT * FROM #ProductTotals;