What is the difference between DELETE and TRUNCATE statements?

In SQL Server, both DELETE and TRUNCATE statements are used to remove data from a table, but they work in different ways and have different effects on the table and the surrounding environment.

DELETE statement is a Data Manipulation Language (DML) command which removes one or more rows from a table based on certain criteria. It is a very flexible statement that allows you to remove specific rows using WHERE clause, or to remove all rows from a table by omitting the WHERE clause.

The syntax of the DELETE statement is as follows:

DELETE FROM table_name
WHERE condition;

For example, suppose you have a table ‘Customers‘ with the following data:

| Id | Name    | Age |
|----|---------|-----|
| 1  | Alice   | 25  |
| 2  | Bob     | 30  |
| 3  | Charlie | 35  |

If you want to delete the row of ‘Bob‘ from this table, you can use the following DELETE statement:

DELETE FROM Customers 
WHERE Name = 'Bob';

After executing this statement, the table ‘Customers‘ will have the following data:

| Id | Name    | Age |
|----|---------|-----|
| 1  | Alice   | 25  |
| 3  | Charlie | 35  |

Note that the ‘DELETE‘ statement only removes the rows that match the specified condition, and leaves the rest of the table intact.

On the other hand, TRUNCATE statement is a Data Definition Language (DDL) command which removes all the rows from a table, also known as "truncate the table". It is much faster than DELETE, because it doesn’t remove the rows one-by-one, but instead deallocates the data pages of the table, effectively resetting the table to its initial state.

The syntax of the TRUNCATE statement is as follows:

TRUNCATE TABLE table_name;

For example, executing the following TRUNCATE statement on the ‘Customers‘ table:

TRUNCATE TABLE Customers;

will remove all the rows from the table, and leave an empty table with the same structure.

Note that TRUNCATE statement doesn’t support the use of WHERE clause, and it cannot be rolled back once executed. Also, since it resets the table to its initial state, it also resets the identity (auto-increment) value of the table, if it has one.

In summary, DELETE is used to remove specific rows based on a criteria, while TRUNCATE is used to remove all rows from a table, effectively resetting the table to its initial state. The choice between them depends on the specific use case, and whether you need to preserve some of the data or not.