MS SQL Server 2012 - DBA Articles

TRUNCATE TABLE - Delete Records from Table

TRUNCATE TABLE is used to delete all rows from a table without logging the individual row deletions.

TRUNCATE TABLE does not delete Indexes, Triggers, Constraints and User Authentication Information of the table from database files. DROP TABLE is implicitly deletes these data objects.

TRUNCATE TABLE statement is uses Table Level Lock or Page Level Lock to execute query and records an entry in the transaction log for each delete data page.

DELETE statement is uses Row Level Lock to execute query and records an entry in the transaction log for each deleted row.

TRUNCATE TABLE statement is clean up data page after executing the query that resource can utilize for any database operation.

DELETE statement is retaining the empty data page that resource cannot utilize for any other database operation.

If the table is created with IDENTITY column, TRUNCATE TABLE statement reset IDENTITTY data value with seed value or default value 1 is used. DELETE statement is retains the IDENTITY counter value of the table.

TRUNCATE TABLE is uses less system and transaction log resources that help to optimize DELETE query execution.

Restrictions

TRUNCATE TABLE cannot execute when the table referring FOREIGN KEY constraint.

TRUNCATE TABLE does not publish data into transactional or merge replication database.

TRUNCATE TABLE does not activate a Trigger, because the operation does not log individual row deletions.

Example

TRUNCATE Records

The following example is used to delete all records from eBusiness database's EMP table information.

-- TRUNCATE Records --

USE eBusiness

TRUNCATE TABLE EMP

* * * * *

Your Comment To AUTHOR