DELETE, TRUNCATE and DROP

Rows, all contents and the object are different levels.

UnderstandStudy an examplePractise

Understand the concept

DELETE can remove selected rows through WHERE. TRUNCATE TABLE removes all rows, accepts no WHERE and resets an identity counter. DROP TABLE removes the object itself. These commands are not interchangeable.

TRUNCATE in SQL Server is not completely unlogged: page deallocations are logged and can be rolled back in an appropriate transaction. Foreign keys, temporal tables and other features can restrict it. This lesson uses temporary objects; production deletion needs authorisation, dependency and recovery planning.

WHEN TO USE IT

Use this distinction to choose the right cleanup and lifecycle operation.

COMMON PITFALL

Assuming TRUNCATE is wholly unlogged or cannot be rolled back.

01 / PRACTICE

One topic. Three levels of depth.

T-SQL / SQL Server 2019+

CREATE TABLE #DeleteDemo (Id int); INSERT INTO #DeleteDemo VALUES (1),(2); DELETE FROM #DeleteDemo WHERE Id = 1; SELECT * FROM #DeleteDemo;

Only temporary row 2 remains.

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.

Try selective deletion versus whole-table deletion in a temporary table.

Show the solution and explanation

Worked solution

CREATE TABLE #CleanDemo (Id int); INSERT INTO #CleanDemo VALUES (1),(2),(3); DELETE FROM #CleanDemo WHERE Id = 2; SELECT COUNT(*) AS Remaining FROM #CleanDemo;

Two rows remain and the object still exists.

RasimAtaberkŞahin

Understand the concept

One topic. Three levels of depth.

Try it yourself first.