How To Truncate Table in SQL Server

Sometimes you may need to empty table content in SQL server, or delete all rows in a table. It is also known as table truncation. In this article, we will look at how to truncate table in SQL Server.

How To Truncate Table in SQL Server

Here are the steps to truncate table in SQL Server using TRUNCATE TABLE command. Please be very careful when you use this command, as there is no way to undo its result.

Here is the syntax of TRUNCATE TABLE command

TRUNCATE TABLE table_name

You just need to specify table name that you want to truncate.

Also read : How to Rename Table in SQL Server

Let us say you have a table sales(id, sale_date, amount)

# create table sales(id int, sale_date date, amount int);

# insert into sales(id, sale_date, amount)
  values(1,'2021-01-01',100),
        (2,'2021-01-02',200);

# select * from sales;
 +------+------------+--------+
 | id   | sale_date  | amount |
 +------+------------+--------+
 |    1 | 2021-01-01 |    100 |
 |    2 | 2021-01-02 |    200 |
 +------+------------+--------+

Also read : How to Update Multiple Columns in SQL Server

Here is the command to truncate sales table in SQL Server.

# truncate table sales;

# select * from sales;
 Empty set (0.00 sec)

As you can see, the table content has been emptied.

Please note, if your table has auto increment column, its value will be reset after TRUNCATE TABLE statement.