Monday, February 20, 2017

Difference between SQL TRUNCATE and DELETE commands

In SQL Server, to remove records from the database table, TRUNCATE and DELETE commands are used. Both the commands are dangerous and should be used with extra caution as they are intended to delete the records. Once deleted, the data is hard to recover.

Syntax of DELETE command:

        DELETE FROM TABLE_NAME [WHERE condition(s)];

TABLE_NAME is the name of the table from where data records are to be deleted.
WHERE is an optional clause and specifies the conditions on which records are to be deleted. If no condition is specified, all the records in the table will be deleted.
Syntax of TRUNCATE command:

TABLE_NAME is the name of the table to be truncated.

TRUNCATE is a Data Definition Language command.
DELETE is a Data Manipulation Language command.
TRUNCATE is executed using a table lock and whole table is locked for remove all records.
DELETE is executed using a row lock; each row in the table is locked for deletion.
TRUNCATE cannot be used with conditions i.e. [Where] clause.
 [Where] clause can be used with DELETE to filter out the data & delete specific records.
TRUNCATE removes all records from a table.
The DELETE command can be used to remove specific/all rows from a table based on WHERE condition.
Minimal logging in transaction log, so it is performance wise faster.
It maintain the log, so it slower than TRUNCATE.
TRUNCATE TABLE removes the data by de-allocating the data pages used to store the table data and records only the page de-allocations in the transaction log.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row.
Identify column, if present in table, is reset to its default value.
Identity of column does not looses even after deleting all the records.
ALTER permission on the table is required to perform TRUNCATE on a table.
DELETE permission on the table is required to run the DELETE command.
Less transaction space is consumed.
Delete consumes more transaction space.
TRUNCATE cannot be used with indexed views
DELETE can be used with indexed views
TRUNCATE TABLE does not activate a trigger because no individual row deletions are logged.
DELETE activates a DELETE trigger, if present.

Share This
Previous Post
Next Post

TekGrabs, established in 2017, is in the business of sharing Information. It provides news, articles and photos about the different technologies, inventions in different domains. Provides information on programming and databases.