SQL Server TRUNCATE TABLE

Learning objective

The objective of this tutorial is to teach you how to remove all the rows from a table in SQL Server in a faster and efficient manner using TRUNCATE TABLE statement in SQL Server.

Introduction to SQL Server TRUNCATE TABLE Statement

In our day to day work in SQL Server, sometimes we may need to remove all the rows from a table. To achieve this, mostly we use the DELETE TABLE statement with the WHERE clause.

The SQL Server TRUNCATE TABLE statement allows you to remove the data from a table.

SQL Server TRUNCATE TABLE Syntax

The syntax of the SQL Server TRUNCATE TABLE syntax is as follows:

TRUNCATE TABLE [database_name.] [schema_name.] table_name
       [ WITH ( PARTITIONS ( partition_number
         | partition_number TO partition_number ) ] ;

In this syntax,

  • database_name – Optional. Denotes the name of the database where the table belongs.
  • schema_name – Optional. If specified, it defines the name of the schema where the table belongs.
  • table_name – Name of the table that you want to truncate.
  • WITH ( PARTITIONS ( partition_number | partition_number TO partition_number ) – Optional. Can only be used with the partitioned table. It specified the number of the partition you want to truncate. In order to list multiple partitions, you can provide the partition numbers in comma separate or ranges. If you try to use this clause with a table that is not partitioned, SQL Server will give an error. This feature is not available in all versions of SQL Server.

Note

  1. In order to execute TRUNCATE TABLE statement, the database user must have privileged on the table.
  2. You cannot truncate a table which is referenced by a FOREIGN KEY.
  3. In the case of truncate, the counter in the IDENTITY column will be reset.

SQL Server TRUNCATE TABLE Example

The following example, create a table named employees in SQL Server and insert some rows into the table:

CREATE SCHEMA company;
GO

CREATE TABLE company.employees
(
    emp_id INT PRIMARY KEY IDENTITY,
    first_name nVARCHAR(50),
    last_name nVARCHAR(20)
);

INSERT INTO company.employees values
   ('Sagar', 'Sharma'),
   ('Shankar','Kumar'),
   ('Akash', 'Ghosh');

Now let’s look at how to delete all the rows from the company.employees table using TRUNCATE TABLE in SQL Server.

TRUNCATE TABLE company.employees;

The above statement is equivalent to the following statement in SQL Server.

DELETE TABLE company.employees;

Both the statement stated above is performed the same task e.g. remove all the rows from the company.employees table. The main difference between the above two statements is that you can roll back the DELETE statement whereas you can’t roll back the TRUNCATE TABLE statement.

Truncate a partitioned table with partition example

Suppose, the above employees table is partitioned table. The following statement is used to truncate a partitioned table in SQL Server.

TRUNCATE TABLE company.employees
    WITH (PARTITIONS (1 TO 4, 6));

In the above example, the company.employees table is a partitioned table and the TRUNCATE TABLE statement will truncate partitions 1 through 4 as well as partition 6 in this partitioned table.

Summary

In this tutorial you have learned how to remove all the rows from a table in SQL Server in faster and efficient manner using TRUNCATE TABLE statement in SQL Server.

Advertisement