Temporary tables and tempdb

Make intermediate-result costs visible.

UnderstandStudy an examplePractise

Understand the concept

A local #table belongs to its session and disappears when the session ends. A global ##table is visible to other sessions and is not used here. Table variables can also consume tempdb resources; they are not guaranteed to be memory-only. Version and compatibility level affect their estimates.

A CTE does not guarantee materialisation. Reusing a large intermediate result may justify a temporary table and measured indexing. tempdb also supports sorts, hash spills and row versioning. Slowness may come from storage, latches, memory or plans. Use descriptive temporary names and remove only the relevant temporary object before rerunning an example.

WHEN TO USE IT

Prepare reusable intermediate data.

COMMON PITFALL

Assuming table variables are faster at every size.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

DROP TABLE IF EXISTS #Paid;
SELECT OrderId, CustomerId INTO #Paid FROM lab.SalesOrder WHERE Status = 'Paid';
SELECT COUNT(*) AS Orders FROM #Paid;

Stores nine orders in the current session.

This page does not execute SQL. Practise in the downloadable SqlStudioLab using SSMS. Before rerunning temporary-table examples, remove only the relevant #table. Views, procedures and functions create persistent lab objects; never use a production connection.

02 / YOUR TURN

Try it yourself first.

Copy product stock into a temporary table and calculate total stock.

Show the solution and explanation

Worked solution

DROP TABLE IF EXISTS #Stock;
SELECT ProductId, Stock INTO #Stock FROM lab.Product;
SELECT SUM(Stock) AS TotalStock FROM #Stock;

Only #Stock is recreated; persistent products remain unchanged.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.