SQL TRUNCATE

The SQL TRUNCATE / SQL SERVER TRUNCATE command is a data definition language statement that can delete all the information from an existing database table without removing the table itself. This will free up space within the table, but users should remember that these changes may not be reversible on some databases. As it’s a DDL statement, there’s no need to commit each step – when you run this command, it automatically commits at completion. Use caution with this, as mistakes cannot generally be undone!

The TRUNCATE statement functions similarly to the DELETE statement, But you cannot use a WHERE clause when using this SQL TRUNCATE command.

Syntax

The basic syntax for the Sql Server Truncate statement is:

TRUNCATE TABLE table_name;

What Does the SQL SERVER TRUNCATE TABLE Statement Do?

The SQL SERVER TRUNCATE TABLE statement in SQL works by removing all rows from an existing table and resetting it to zero entries. This means that after running a truncate statement on the table, you will no longer have any row records stored there.

When you truncate a table, its structure of it, including columns, indexes, constraints, and relationships, is still intact. It’s similar to erasing data from a table but leaving the overall shape in place.

SQL Truncate Example

Let us take an example for a better understanding of SQL server truncate statements. Here are the Person table details:

SQL TRUNCATE 1

Now, let us run the SQL truncate statement.

TRUNCATE TABLE Person;
SELECT * FROM Person;

After running the above script, all the rows will be deleted.

SQL TRUNCATE 2

Note: The TRUNCATE clause doesn’t support the WHERE clause.

Truncating Large Tables

Microsoft SQL Server can delete or empty a table with more than 128 extents without acquiring locks. This makes it possible to truncate/drop large tables quickly and efficiently.

Conclusion:

This tutorial has provided the information needed to use SQL Server TRUNCATE TABLE statements to quickly remove all data from a table, especially if it is large. If you have any questions after reviewing this article, please comment in the comment section.

Avatar for Softwaretestingo Editorial Board

I love open-source technologies and am very passionate about software development. I like to share my knowledge with others, especially on technology that's why I have given all the examples as simple as possible to understand for beginners. All the code posted on my blog is developed, compiled, and tested in my development environment. If you find any mistakes or bugs, Please drop an email to softwaretestingo.com@gmail.com, or You can join me on Linkedin.

Leave a Comment