<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

The Difference Between Truncation and Deletion in Microsoft SQL Server

Published on
20,549 Points
8,849 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 55

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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook 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 67

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

Fundamentals of JavaScript

Learn the fundamentals of the popular programming language JavaScript so that you can explore the realm of web development.

Join & Write a Comment

This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month