Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server

Using DELETE and TRUNCATE TABLE statements to delete data in SQL Server

When writing Transact-SQL statements that delete SQL Server data, either the TRUNCATE TABLE or DELETE statement can be used. In fact, the TRUNCATE TABLE is actually more efficient than DELETE. Still, it can't be used in every circumstance. To understand the differences between the two statements, let's look at examples of how they work.

Setting up the examples

In these examples, I have used the following code to create the Employees table in the AdventureWorks2008 sample database:

USE AdventureWorks2008;

IF OBJECT_ID (N'Employees', N'U') IS NOT NULL
DROP TABLE dbo.Employees;

CREATE TABLE dbo.Employees
(
  EmpID INT IDENTITY PRIMARY KEY,
  EmpName

    Requires Free Membership to View

    Login

    By submitting your registration information to SearchSQLServer.com you agree to receive email communications from TechTarget and TechTarget partners. We encourage you to read our Privacy Policy which contains important disclosures about how we collect and use your registration and other information. If you reside outside of the United States, by submitting this registration information you consent to having your personal data transferred to and processed in the United States. Your use of SearchSQLServer.com is governed by our Terms of Use. You may contact us at webmaster@TechTarget.com.

Premium Access

Register now for unlimited access to our premium content across our network of over 70 information Technology web sites.
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

This was first published in November 2009

NVARCHAR(100) NOT NULL,
  EmpJobTitle NVARCHAR(50) NOT NULL
);

Note: The examples I use in this article were written against a local instance of SQL Server 2008. However, I also tested the statements against an instance of SQL Server 2005.

To try these examples, first make sure the AdventureWorks2008 sample database is installed (For SQL Server 2005, use the AdventureWorks database and change the database name in the sample code), then create the table and run the following INSERT statement:

INSERT dbo.Employees (EmpName, EmpJobTitle)
SELECT
   (FirstName + ' ' + LastName) AS EmpName,
   JobTitle
FROM AdventureWorks2008.HumanResources.vEmployee
WHERE LastName = 'Brown';

In the examples, I simply retrieve a few rows from the Employee view and insert them into the new table. The following SELECT statement can be run to confirm that the data has been added to the Employees table:

SELECT * FROM dbo.Employees;

The SELECT statement should return the following results:

EmpID  EmpName      EmpJobTitle
1      Eric Brown   Production Technician - WC40
2      Jo Brown     Production Supervisor - WC60
3      Kevin Brown  Marketing Assistant

After the Employees table is created and populated with data, the data can be deleted from the table.

TRUNCATE TABLE statement examples

Let's start by using the TRUNCATE TABLE statement. This statement can only be used to delete all rows of data from the target table. The statement itself has a few syntax elements: the TRUNCATE TABLE keywords and the name of the table (qualified to the degree necessary).

In the following example, the TRUNCATE TABLE statement is used to delete all the rows in the Employees table:

TRUNCATE TABLE dbo.Employees;

As you can see, the statement is quite simple. (Just be certain you want to delete all the rows before running it.)

If the SELECT statement shown above is now rerun, no data would be returned. However, if the INSERT statement was run again followed by the SELECT statement, the following results would be retrieved:

EmpID  EmpName      EmpJobTitle
1      Eric Brown   Production Technician - WC40
2      Jo Brown     Production Supervisor - WC60
3      Kevin Brown  Marketing Assistant

These are the same results we saw when the first time the SELECT statement was run.

More T-SQL advice
SQL language crash course (just enough to be dangerous)

Working with IntelliSense in SQL Server 2008 Management Studio

One thing you might have noticed is the EmpID values are the same as in the original result set. The EmpID column is an IDENTITY column that uses the default seed data, which means that the first number assigned to the column is 1. When you run the TRUNCATE TABLE statement against a table with an IDENTITY column, it resets the numbering to start with the original seed data. As a result, each time you run TRUNCATE TABLE against the Employees table, the first row inserted will have an EmpID value of 1.

DELETE statement examples

You can also use a DELETE statement to delete all the data from a table, as shown in the following example:

DELETE FROM dbo.Employees;

Once again, only the keywords (DELETE FROM) and the name of the table need to be specified. If I repopulate the table and then run the SELECT statement, however, my results are slightly different:

EmpID  EmpName      EmpJobTitle
4      Eric Brown   Production Technician - WC40
5      Jo Brown     Production Supervisor - WC60
6      Kevin Brown  Marketing Assistant

As you can see, the IDENTITY column values now start with 4 rather than 1. Even if the DELETE statement was used to remove all data, the IDENTITY values are still incremented according to the last value used.

It is important to note that the differences between the DELETE statement and the TRUNCATE TABLE statement go beyond the IDENTITY value. When deleting all data from a table, the TRUNCATE TABLE statement is more efficient than the DELETE statement:

  • A TRUNCATE TABLE statement tends to use fewer locks than a DELETE statement. When a TRUNCATE TABLE statement is used, SQL Server applies table and page locks -- but not row locks, as a DELETE statement does.
  • A TRUNCATE TABLE statement uses less transaction log space than a DELETE statement. When a TRUNCATE TABLE statement is used, SQL Server de-allocates the data pages and records only the de-allocations in the transaction log. When a DELETE statement is used, SQL Server makes an entry into the transaction log for each deleted row.
  • A TRUNCATE TABLE statement leaves no pages in a table, whereas a DELETE statement can leave empty pages.

Regardless of these differences, both the TRUNCATE TABLE and DELETE statements remove only data and do not impact the table structure. Indexes, constraints, and columns are left untouched.

While a TRUNCATE TABLE statement is more efficient than a DELETE statement, there are restrictions that govern the use of TRUNCATE TABLE. For example, you should not use a TRUNCATE TABLE statement against a table under the following conditions:

  • An indexed view specifies the table in its definition.
  • Transaction or merge replication is used to publish the table.
  • The table is referenced by a foreign key constraint (unless it is a self-referencing foreign key).
  • The table's IDENTITY values must be preserved and consistently incremented.
  • Only specific rows are to be deleted from the table, and not the entire dataset.

The last item is of particular concern because it points to the fact that a TRUNCATE TABLE statement cannot be used to delete specific rows from a table. In such cases, use a DELETE statement and qualify the statement with a WHERE clause. In the following example, I use a DELETE statement to remove the row with an EmpID value of 6:

DELETE FROM dbo.Employees
WHERE EmpID = 6;

As you can see, I qualify the DELETE statement with a WHERE clause that specifies the value to be deleted. If the SELECT statement is now run against the Employees table, the following results will be received:

EmpID  EmpName      EmpJobTitle
4      Eric Brown  Production Technician - WC40
5      Jo Brown    Production Supervisor - WC60

More complex DELETE statements can also be created. The important point to keep in mind is that, when possible, use TRUNCATE TABLE when deleting all rows from a table. Otherwise, use DELETE.

For more details about the DELETE statement, check out DELETE (Transact-SQL) in SQL Server Books Online. For details about the TRUNCATE TABLE statement, see TRUNCATE TABLE (Transact-SQL).

ABOUT THE AUTHOR
Robert Sheldon is a technical consultant and the author of numerous books, articles, and training material related to Microsoft Windows, various relational database management systems, and business intelligence design and implementation. You can find more information at  http://www.rhsheldon.com.

Join the conversationComment

Share
Comments

    Results

    Contribute to the conversation

    All fields are required. Comments will appear at the bottom of the article.

    Disclaimer: Our Tips Exchange is a forum for you to share technical advice and expertise with your peers and to learn from other enterprise IT professionals. TechTarget provides the infrastructure to facilitate this sharing of information. However, we cannot guarantee the accuracy or validity of the material submitted. You agree that your use of the Ask The Expert services and your reliance on any questions, answers, information or other materials received through this Web site is at your own risk.

    Back to top