What is the Difference Between TRUNCATE & DELETE Clauses in SQL Server

Qns by Labels	Differences	SQL Server	SSIS	SSRS	T-SQL

__

Post a Job! $5 for 60 days

• TRUNCATE is a DDL (Data Definition Language) command, whereas DELETE is a DML (Data Manipulation Language) command.
• TRUNCATE removes all the records from a table without making a log entry for individual row deletions (Does make log entries about data page deallocation etc, which can be used for ROLLBACK of a TRUNCATE command), whereas DELETE removes all or selected records (based on absence or presence of a WHERE condition) from a table by making a log entry for individual row deletion. Hence TRUNCATE is faster than DELETE.
• TRUNCATE removes all the records from a table and a WHERE clause or filter condition cannot be used with TRUNCATE, whereas DELETE can remove selected records or all records based on whether a WHERE clause (Optional) is used or not used respectively.
• TRUNCATE cannot be used on a table if it satisfies one of the following conditions:
• Table is referenced by one of more FOREIGN KEY constraints
• Table is marked / enabled for Replication
• TRUNCATE resets IDENTITY in any of the columns in a table, whereas DELETE does not reset the IDENTITY.