Difference between SQL DELETE and TRUNCATE Statement

In this post, I will explain the Difference between SQL Delete and Truncate Statement.

In SQL, there are different ways to delete rows from a table. We can use Delete Statement or Truncate Statement to delete rows.

Here are some important differences between delete and truncate statement:

1.  Delete statement is used to delete a single or multiple rows from a table, we can use where clause with delete statement to delete selected row(s). Truncate statement is used to remove all rows from a table, it performs the same function as a Delete statement without a Where clause.

2. Where clause with delete statement is optional. Truncate statement is without where clause.

3. Delete operation on a table will not reset the identity column counter. Truncate operation on a table resets identity column counter.

Example -

Delete Statement

DELETE FROM tbl_Employee WHERE Id = 10

Truncate Statement

TRUNCATE TABLE tbl_Employee

Read More

SQL Truncate Statement

In this post, I will explain the SQL Truncate Statement.

The Truncate statement is used to remove all records from a table. It performs the same function as a DELETE statement without a WHERE clause.

Few things to Note about Truncate -

1. Truncate operation on a table resets identity column counter

2. If table is referenced by a foreign key, then one cannot perform Truncate operation on table

3. To perform Truncate operation on a table, one should have a ALTER Table permissions

4. Truncate table is a fast way to clear all records from a table

5. Truncate operation cannot be rolled back if it is committed

Example -

TRUNCATE TABLE tbl_Employee

Read More