What is the difference between Truncate Table and delete in SQL?

Introduction

Both commands will allow you to delete records from a table but you need to be aware of what each can and can't do before using either of them. Truncate Table has a very specific sole function in the database, it will clear the entire table that you specify and reset the Identity seed if one exists. There is no Where clause so choose which records to clear. If you provided a where clause to the statement, it will error and the command will fail. To use, you simply do :-

Truncate Table ExampleTable

Delete allows you to be more specific which records to remove and allows you to have a where clause. If you are going to do a comparison between the two methods, you will should not have a where clause. A Delete without a Where clause will delete EVERY record from the database so only run if you're absolutely sure you wish to delete every record.

Delete from ExampleTable

Summary, Things to be aware.

• Whilst its possible to recover deleted records when you use the delete command, you are out of luck when you clear the table using Truncate Table.
• Using Delete will not reset the Identity seed and the next record will continue from where the last one set off from.
• Truncating the table is faster than delete but as mentioned, theres recover.

Tags - SQL

Last Modified : June 2023