Delete and Truncate

 

There is a common question like what are the differences between Delete and Truncate statements of SQL server.

Yes there are a lot of differences between them, but I have like to mention the most important differences between them in my short article.

1.    First we understand what type of command they are. DELETE is the DML command and TRUNCATE is the DDL Command.

2.    No WHERE condition is used with TRUNCATE, so it effects on entire Table Objects.

3.    The TRUNCATE command NOT fire any TRIGGER related to Table, as it is not delete data row wise.

4.    Drop all object's statistics and marks like High Water Mark free extents and leave the object really empty with the first extent. zero pages are left in the table

5.    If the table contains an identity column, the counter for that column is reset to the seed value that is defined for the column

6.    Restrictions on using Truncate Statement

a.    Are referenced by a FOREIGN KEY constraint

b.    Participate in an indexed view.

c.    Are published by using transactional replication or merge replication.

Now What the Advantage of TRUNCATE rather than DELETE

1.    It is Very FAST

2.    TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data.

 

Hope the Article is quite informative and thanking you to give your valuable time in it.

 

Posted by: MR. JOYDEEP DAS