Click here to Skip to main content
13,139,569 members (55,068 online)
Click here to Skip to main content
Add your own
alternative version

Tagged as

Stats

9.6K views
9 bookmarked
Posted 18 Sep 2014

TRUNCATE or DELETE a Table?

, 18 Sep 2014
Rate this:
Please Sign up or sign in to vote.
TRUNCATE or DELETE a Table?

A common question is what is the difference between truncating a table and deleting a table. The short answer is that they both do the same thing, but in different ways. So consider that you have a table that contains 1,000,000 rows and you need to remove all of the rows.

Option 1 is to use DELETE tablename. Using DELETE with no filter will remove every row in the table as is to be expected while leaving the table intact, but as each row is being deleted, the row will be recorded in the transaction log, 1,000,000 rows = 1,000,000 records in the log. Some other traits of DELETE are:

  1. DELETE uses a row lock each row in the table is locked for deletion
  2. After DELETE is run, the table can still contain empty data pages
  3. If the table contains an IDENTITY column, the counter of that column will remain

Option 2 is to use TRUNCATE TABLE tablename which will also delete all rows in the table, but the primary difference between this and DELETE is the logging. TRUNCATE TABLE deallocates the data pages and only logs the deallocation of the pages. Other traits of TRUNCATE includes:

  1. It always locks the table, including a schema lock, and page, but not each row
  2. Without exception, all pages are deallocated from the table, including empty pages
  3. Once truncated, an IDENTITY column is reset to the seed column defined by the table, if no seed is defined, the value is set to 1.

Generally, TRUNCATE is more efficient then DELETE as it has reduced logging and fewer locks, but there are some limitations. TRUNCATE cannot be used with:

  1. A table that is referenced by a foreign key
  2. Participate in indexed views
  3. Are published using transactional or merge replication
  4. Truncate will not activate a trigger, due to how TRUNCATE is logged
  5. Requires ALTER TABLE permission

One common misconception is that because of the way that TRUNCATE is logged, it cannot be rolled back when included in an explicit transaction and this is not the case at all. TRUNCATE is still logged just differently than DELETE. In order for SQL to maintain its ACID properties of a database, the ability to rollback, implicitly or explicitly, must be available.

To demonstrate this, review the T-SQL code below:

USE MASTER;
GO

IF EXISTS(SELECT * FROM sys.databases WHERE name = ‘LogGrowth’)
BEGIN
DROP DATABASE LogGrowth
END

CREATE DATABASE LogGrowth;
GO

ALTER DATABASE LogGrowth SET RECOVERY SIMPLE;
GO

SELECT name,
log_reuse_wait_desc,
CASE recovery_model
WHEN 1 THEN ‘FULL’
WHEN 2 THEN ‘BULK_LOGGED’
WHEN 3 THEN ‘SIMPLE’
END AS recovery_model
FROM sys.databases
WHERE name = ‘LogGrowth’;
GO

–Transactions
USE LogGrowth;
GO

IF NOT EXISTS(SELECT * FROM sys.tables WHERE name = ‘Transact’)
BEGIN
CREATE TABLE Transact(
col1   CHAR(50)
);
END
GO

–INSERT 50 rows in the Transact table
INSERT Transact
VALUES(‘This is gonna be gone’);
GO 50

SELECT COUNT(*)
FROM Transact;

rowcount

Now explicitly begin a transaction to TRUNCATE the table and show the row count and immediately roll the transaction back:

BEGIN TRAN
TRUNCATE TABLE Transact;
SELECT COUNT(*)
FROM Transact;
ROLLBACK TRAN

rowcount0

After rolling back the transaction, you can verify that the 50 records still exist by executing another SELECT statement with COUNT(*):

SELECT COUNT(*)
FROM Transact;

rowcount

The T-SQL code above can be downloaded here.

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

derekman9707
Database Developer
United States United States
David retired as a Sergeant with the Cape Coral Police Department after 22 years of service. His final 10 years of duty were as a database administrator and developer in the Administrative Services Division. He began his career with the police department in 1990 in the patrol division and worked various assignments until being promoted to Sergeant in 1998. Based on his education and experience David was assigned to Administrative Services in 2002 and was responsible for database administration, software integration, and development for public safety. David’s primary focus and expertise is with SQL Server, reporting services, integration services, and analysis services, and he was recognized for his work by SQL Server Magazine as “Innovator of the Year” runner up in 2007. David is an MCITP for SQL Server 2005 and 2008 in both database administration and business intelligence and is a Microsoft Certified Trainer. He regularly posts on the MSDN SQL Server forums where he also serves as a moderator, and is a contributor at SQLCLR.net. In addition to his knowledge of SQL Server David works as a software developer using VB.net and C# and has worked extensively in SharePoint development.

You may also be interested in...

Comments and Discussions

 
GeneralMy vote of 1 Pin
Member 1053271522-Sep-14 4:38
memberMember 1053271522-Sep-14 4:38 
GeneralRe: My vote of 1 Pin
derekman970722-Sep-14 7:58
memberderekman970722-Sep-14 7:58 
GeneralRe: My vote of 1 Pin
KP Lee23-Sep-14 17:58
memberKP Lee23-Sep-14 17:58 
QuestionYou missed one thing Pin
KP Lee20-Sep-14 3:49
memberKP Lee20-Sep-14 3:49 
AnswerRe: You missed one thing Pin
derekman970722-Sep-14 5:29
memberderekman970722-Sep-14 5:29 
GeneralRe: You missed one thing Pin
KP Lee23-Sep-14 17:51
memberKP Lee23-Sep-14 17:51 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.170915.1 | Last Updated 18 Sep 2014
Article Copyright 2014 by derekman9707
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid