DELETE AND TRUNCATE DIFFERENCES.

TRUNCATE Command
It will remove all the rows from the table and there will be no data in the table.
Truncate a table deallocates the extents and bulk sets 8 PFS bytes at a time.
Ghost cleanup is not involved in Truncate command as the rows are not deleted.
We cannot have where clause with truncate.
Truncate removed all rows but the table structure, columns, constraints, indexes and so on.
Truncate can be rolled back if used inside a transaction.
It is a DDL Command.
Truncate resets the identity of the table.
Truncate is faster and uses fewer system and transaction log resources than delete.
You cannot truncate table which is referenced by a foreign key constraint.
You cannot use TRUNCATE TABLE on tables that:-
a) Are referenced by a FOREIGN KEY constraint. (You can truncate a table that has a foreign key that references itself.)
b) Participate in an indexed view.
c) Are published by using transactional replication or merge replication.
Delete Command
Delete command removes the rows from a table based on the condition that we provide with a where clause.
Delete removes rows one at a time and record an entry in the transaction log ( .ldf file ) for each deleted row.
Delete can be rolled back.
It is DML command.
Delete does not resets the identity of the table.
Delete uses more resources than truncate.
Delete can be used with where clause or without where clause.

Thank You.

Pawan Khowal

Pawan is a SQL Server Developer. If you need any help in writing code/puzzle or training please email at – pawankkmr”AT”gmail.com. Meanwhile please go throgh the top pages from his blog.

Page Detail URL
☛ SQL Advance Puzzles https://msbiskills.com/tsql-puzzles-asked-in-interview-over-the-years/
☛ SQL Tricky Queries https://msbiskills.com/sql-puzzles-finding-outputs/
☛ SQL Server Perfomance tuning Articles https://msbiskills.com/sql-performance-tuning/
☛ SQL Server Articles https://msbiskills.com/t-sql/
☛ SQL Interview Questions & Answers https://msbiskills.com/sql-server-interview-questions/

My SQL Groups on Facebook:

1. If you like this post, you may want to join my SQL SERVER Interview Puzzles/Interview Questions on Facebook: https://www.facebook.com/groups/1430882120556342/

2. If you like this post, you may want to join my SQL Server Puzzles on Facebook:
https://www.facebook.com/groups/206594023090781/

My SQL Page on Facebook:

2. For all the updates you may follow my page -> https://www.facebook.com/MSBISkillscom-1602779883299222/

Enjoy !!! Keep Learning

Http://MsbiSkills.com