Different between delete and truncate command in sql?

Naresh Beniwal

3y1.1kSQL

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Rajanikant Hawaldar3y ago
Accepted answer
1. DELETE is a DML command while TRUNCATE is a DDL command.
2. You require to have DELETE permission on a table to use the Delete command, But you require Alter table permissions to TRUNCATE a table.
3. DELETE command deletes records one by one and makes an entry for each and every deletion in the transaction log, whereas TRUNCATE de-allocates all the pages and makes an entry for the de-allocation of pages in the transaction log.
4. TRUNCATE is faster compared to delete as it makes less use of the transaction log.
5. TRUNCATE reseeds identity values, whereas delete does not.
6. TRUNCATE command does not fire the trigger which could result in inconsistent data because ON DELETE/UPDATE triggers would not fire. But a DELETE command always fires the trigger.
7. You cannot specify a WHERE clause in a TRUNCATE TABLE statement, it is all or nothing.
8. You can use the Delete statement with the indexed views but you cannot use the TRUNCATE command with the indexed views.
9. Delete command retains the object statistics and allocated space. But since Truncate deallocates all data pages of a table, it removes all statistics and allocated space as well.
10. TRUNCATE is not possible when a table is referenced by a Foreign Key or tables are used in replication or with indexed views.
Brahma Prakash Shukla3y ago
TRUNCATE and the DELETE statement both come under SQL query for delete operation. They perform deletion operations on unwanted records or rows of a table. In the DELETE statement, the rows from the table get deleted with a condition. In simple words, one or more rows can be deleted at once. but in the TRUNCATE command, all the rows get deleted at once.

When we use the DELETE query, a log is created for every row in the transaction log. This way we can get back the records using ROLLBACK before COMMIT. Also, when using TRUNCATE, we can get the records back using ROLLBACK. The difference is that TRUNCATE only logs the deallocation of the page where data is stored.
Jignesh Kumar3y ago
Please refer these link which has good informatice details,

https://www.c-sharpcorner.com/blogs/difference-between-truncate-delete-and-drop-in-sql-server1

https://www.mssqltips.com/sqlservertip/4248/differences-between-delete-and-truncate-in-sql-server/