T-SQL 101: 140 Truncating a SQL Server Table vs Deleting All Rows

Deleting rows from a table can take quite a long time because there is a lot of work going on under the covers. One way that you can just completely empty a table is by executing a TRUNCATE TABLE statement.

While this is fast, there are two issues with it:

First, if the table had delete triggers, and I used a DELETE statement, those triggers would fire. But if I say TRUNCATE TABLE instead, it just quickly nukes the entire contents of the table, by deallocating all the storage used for table rows. But those triggers wouldn’t fire.

Second, it needs more permissions than DELETE. If a user has permission to delete rows, they might not have permission to truncate the table. Far fewer users can normally do that. TRUNCATE TABLE is an administrative function.

Learning T-SQL

It’s worth your while becoming proficient in SQL. If you’d like to learn a lot about T-SQL in a hurry, our Writing T-SQL Queries for SQL Server course is online, on-demand, and low cost.

2025-04-05