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
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.
- In order to execute
TRUNCATE TABLEstatement, the database user must have privileged on the table.
- You cannot truncate a table which is referenced by a
- 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.
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.