<

The Difference Between Truncation and Deletion in Microsoft SQL Server

Published on
20,432 Points
8,732 Views
17 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
The removal of data is a common activity inside all database systems. There are two mechanisms to remove records inside the SQL Server database engine: the DELETE statement and the TRUNCATE TABLE statement. While both of these statements are similar in that they are used to remove data, there are distinct differences between the two.

DELETE

The DELETE statement removes records from a database table at a row-level. When a record is removed using DELETE, entries are recorded in the transaction log for the operation, constraints are checked, and any triggers are fired. Records that have been accidentally removed using DELETE are typically able to be recovered with an added amount of effort provided a database recovery model is used to make such actions available. While DELETE can be used to remove all of the records from a table it is much more common to remove a smaller subset of records that match specific criteria. DELETE may be used in single-table statements, or as a method to remove data from a single table when joining to one or more related tables. DELETE permissions on tables can be specified to specific users without the need to assign object ownership to the user. A DELETE statement does not modify the seeding of auto-incrementing columns if the table contains them.

TRUNCATE

The TRUNCATE statement deallocates all data pages from a table, rendering the entire table completely empty after execution. This operation is significantly faster than the DELETE statement due to the page deallocation and that very minimal amount of information that is tracked in the transaction log. Because it is very minimally logged, once the TRUNCATE statement has committed it cannot be undone. TRUNCATE is a data-definition language (DDL) operation, which means that ALTER TABLE or higher permissions are necessary to execute it. A 'TRUNCATE TABLE' permission does not exist. If the truncated table has auto-incrementing columns, they will be reseeded according to their original definition. There are also additional restrictions on a TRUNCATE statement. It may not be issued against tables in the the following situations:

when the table is referenced by an index view.
when foreign keys reference the table to be truncated.
when the table is published for replication.
when the table belongs to a database that is being log-shipped.

WHEN TO USE

Your specific situation should dictate whether to use DELETE or TRUNCATE to remove data. DELETE is by far the most commonly used method to remove data as it allows you to specify the records you want to remove using JOINs and other necessary criteria. Blocking may occur when removing large amounts of data using DELETE, so care must be taken when designing such statements. Permissions for DELETE operations are easily assigned to users or roles without the ownership necessary for the underlying tables. TRUNCATE is more of an administrative function to be used in those situations where all data needs to be removed from a table quickly and efficiently.
17
Comment
Author:chapmandew
7 Comments
 
 

Administrative Comment

by:Kevin Cross
chapmandew:

I know a number of intermediate database administrators/developers that didn't even know about the TRUNCATE keyword, so I definitely believe this to be very useful and informative.  Those that may not know about TRUNCATE and/or how DELETE really works with respect to the transaction log and such will hopefully appreciate the effort.

I definitely did as Page Editor and a Zone Advisor within the SQL / Databases topic areas, and so I happy to announce your article having been chosen as "Community Picks" as well as "EE Approved".

Thanks for your time.  You absolutely have my "Yes" vote above!

Respectfully yours,

mwvisa1
Page Editor
0
 
LVL 54

Expert Comment

by:Mark Wills
Like all Tim Chapman Articles, this is another good example of simple to the point explanation of how and when to use either method for removing data. Gets my "Yes" vote.
0
 
LVL 12

Expert Comment

by:w00te
I was just browsing the SQL articles and came across this.  I thought I'd give it my YES as I've seen this question in at least 3 interviews now and have had to know it in prior situations myself; everyone who doesn't know what truncate means should read this!
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
LVL 22

Expert Comment

by:dportas
"once the TRUNCATE statement has committed it cannot be undone."

Not sure what is meant by this statement. The same can be said of both DELETE and TRUNCATE: a point-in-time restore is the only way to undo the operation once committed.
0
 
LVL 60

Author Comment

by:chapmandew
I was really talking in terms of recovering it from a 3rd party tool (w/o having to recovery to a point in time).  But, as it so happens, I wasn't 100% correct.  It seems that ApexSQL can do some tricky things now w/ their Log product, and it is able to recover a truncated table (through some sort of magic). :)

http://knowledgebase.apexsql.com/2009/08/recover-from-rogue-truncate-table.htm
0
 
LVL 66

Expert Comment

by:Jim Horn
Excellent illustration of TRUNCATE.  Voted Yes.
0
 
LVL 13

Expert Comment

by:magarity
SQL Server's TRUNCATE is a strange hybrid between DML and DDL because you can roll it back before a COMMIT within a transaction.  On most other DBMS it is strictly DDL because it cannot be trapped within a transaction.  Most people keep auto-commit enabled so it appears to be DDL in SQL Server, but it isn't. Try:

BEGIN TRANSACTION TRUNCTEST;
TRUNCATE TABLE mytable;(some table that has some rows)
SELECT * FROM mytable;
ROLLBACK TRANSACTION TRUNCTEST;
SELECT * FROM mytable;

On Oracle, for comparison, the table would still be empty after the rollback because TRUNCATE there is immediate and unrecoverable.

SQL Server's own documentation refer to TRUNCATE as a "minimally logged" operation and avoids identifying it either way as DDL or DML.
0

Featured Post

Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

Join & Write a Comment

Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month