Should I be using TRUNCATE TABLE or DELETE FROM in SQL Server?

All topics about programming / development in T-SQL or other languages for SQL Server.
Post Reply
cascade
Posts: 5
Joined: Tue Jul 02, 2019 7:37 pm

Sat Jul 13, 2019 10:48 am

Can anybody tell me if I should be using TRUNCATE TABLE or DELETE FROM in SQL Server?

What are the benefits of using one over the other? Which is the best for performance?

Code: Select all

TRUNCATE TABLE dbo.TableName
vs

Code: Select all

DELETE FROM dbo.TableName
DaveP5
Posts: 29
Joined: Sat Jun 22, 2019 7:58 pm
Answers: 1

Sat Jul 13, 2019 10:57 am

TRUNCATE doesn't generate any rollback data, which makes it lightning fast. It just deallocates the data pages used by the table.

However, if you are in a transaction and want the ability to "undo" this delete, you need to use DELETE FROM, which gives the ability to rollback.


TRUNCATE TABLE will reset your identity to the initial seed, whereas DELETE FROM will carry on incrementing from where it left off.


TRUNCATE TABLE doesn't log the transaction. That means it is lightning fast for large tables. The downside is that you can't undo the operation.

DELETE FROM logs each row that is being deleted in the transaction logs so the operation takes a while and causes your transaction logs to grow dramatically. The upside is that you can undo the operation if need be.
BigredDBA
Posts: 1
Joined: Mon Jul 15, 2019 6:07 pm

Mon Jul 15, 2019 6:35 pm

Truncate can be rolled back within a transaction, the same as a delete. The difference is that delete logs rows, which is slow and generates lots of log data. Truncate just flags pages as deallocated but it holds a lock until commit so it can be rolled back. Other differences are that it resets identity seeds, can't have a where clause, doesn't fire triggers, table can't be referenced by a foreign key or indexed view, be published in replication.

It needs higher permissions than delete but can be put in an sp and use execute as. Although it doesn't support a WHERE clause it will work against individual partitions which is sort of a where clause.

In other DBMS's it can't be rolled back which leads to confusion.
cascade
Posts: 5
Joined: Tue Jul 02, 2019 7:37 pm

Mon Jul 15, 2019 7:32 pm

Thank you @BigredDBA!
Post Reply

Social Media