DROP TABLE versus TRUNCATE TABLE

This lesson uses DDI_SQL_Training dataset v1.0.0 on SQL Server 2019 or later. Distinguish removing rows from removing the table definition. Run changing examples only on a disposable training instance. The example uses a session-local object or rolls back its changes.

Run the example

CREATE TABLE #Scratch (ID int NOT NULL PRIMARY KEY);
INSERT INTO #Scratch VALUES (1), (2);
TRUNCATE TABLE #Scratch;
SELECT COUNT(*) AS RowsAfterTruncate FROM #Scratch;
DROP TABLE #Scratch;

Read it step by step

1. TRUNCATE removes all rows from the temp table while preserving its definition.
2. COUNT(*) can still query the empty table.
3. DROP TABLE then removes the table definition too.

Check the result

The canonical dataset is unaffected.

Practice

Create and drop a temp table, then check that OBJECT_ID reports it absent.

CREATE TABLE #Scratch (ID int NOT NULL);
DROP TABLE #Scratch;
SELECT CASE WHEN OBJECT_ID(N'tempdb..#Scratch') IS NULL
            THEN 1 ELSE 0 END AS WasDropped;