Truncate Table Statement in SQL Server and Difference Between Delete and Truncate Table(TSQL)

 

If we want to delete all records in the Table, we can do this with the Truncate Table command.

You can use the truncate table command as below:

You may also think that you can do the same by not specifying the where condition by using the delete command. There are some differences between the Delete command and the Truncate Table command. You can find these differences below.

You can delete only some rows in the table by specifying the where condition in the delete command. You cannot specify a condition in the Truncate Table command. It deletes all rows in the table when you run it.
The delete command runs slower than the truncate table command. The Truncate table command runs faster than the delete command.
If you delete all of the records in the table with the delete command and the auto-incremental identifier property is set in a column in the table, identitiy does not start from number 1. When a new record is added to the Table, it continues to increase starting with the last identitiy number before the delete command. If you delete all of the records in the table with the Truncate Table command and the auto-incremental identifier property is set in a column in the table, identitiy starts from number 1.

 

You can find detailed information about the identity feature in the article “Identitiy Insert On SQL Server“.

dbtut
Author: dbtut

We are a team with over 10 years of database management and BI experience. Our Expertises: Oracle, SQL Server, PostgreSQL, MySQL, MongoDB, Elasticsearch, Kibana, Grafana.

Leave a Reply

Your email address will not be published. Required fields are marked *