- T-SQL Command: DELETE FROM [TableName] WHERE [Condition]
- DELETE is DML command hence it can be rolled back.
- A WHERE clause can be used to provide the facility of conditional-based deletion
- Triggers can be fired
- When we use delete command on a table having IDENTITY column, it will not reset the counter of identity column. Instead, it maintains the same counter for new rows.
- DELETE is slower than Truncate because it logs deleted data into transaction log files. DELETE command removes one row at a time and logged into transaction log files.
- T-SQL Command: TRUNCATE TABLE [TableName]
- TRUNCATE is DDL command hence it can't be rolled back.
- A WHERE clause can't be used for conditional-based deletion. It removes entire rows at once.
- Triggers will not be fired - because Truncate doesn't remove any row, it just deallocates the data pages
- When we use truncate command on a table having IDENTITY column, it will reset the counter used by identity column to the seed value defined for the column.
- TRUCATE is faster than DELETE because it removes data directly without copying into transaction log files. In fact, TRUNCATE is also a logged operation but it logs the deallocation of the data pages in which the data exists - means your data rows still actually exist in the data pages, but the extents have been marked as empty for reuse. Thats why it is faster than DELETE command.
- Can't use truncate command if table has foreign key constraint, even if the referring table has no records. You will get below error: Cannot truncate table 'TableName' because it is being referenced by a FOREIGN KEY constraint.
- Can't use truncate command if table has a column that participate in Indexed view.