Tuesday, September 7, 2010

Difference between Truncate and Delete

Difference between Delete and Truncate:

                Delete Logged operation on per row basis. [Every record logged and physically deleted].
                Delete any row that will not violate constraint, while leaving the foreign key or any other constraints.
                Truncate is also a logged operation in different way [ SQL Server 2005 and above].
                Truncate logs the de-allocation of the data pages in which the data exists. [Data exists in pages, marked as empty for reuse].
                Truncate is faster than Delete.
                If table have any Foreign Key not able to perform truncate operation.
                Truncate reset identity columns
                Delete perform trigger operations.

No comments:

Post a Comment