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.

Need a reporting tool for SQL Server? Ubiq makes it easy to visualize data in minutes, and monitor in real-time dashboards. Try it Today!

mm

About Ubiq

Ubiq is a dashboard & reporting platform that makes it easy to get insights from data. Build dashboards, charts & reports for your business in minutes. Try it for free today!