Truncate Table in SQL

The DELETE statement in SQL deletes one or more records from the table, and you can provide a WHERE clause to the DELETE statement, and any conditions that can be used in the SELECT statement in SQL can be used in the delect WHERE clause. The syntax format for the DELETE statement is:

DELETE [from] {table_name|view_name} [WHERE condition].

If you do not specify a WHERE clause for the DELETE statement, all records in the table are deleted. In general, when you execute a DELETE statement, you must carefully consider the WHERE clause. If you do need to delete all the records in the table, you should use the TRUNCATE TABLE statement.

Why use the TrunCATE TABLE statement instead of the DELETE statement? When you use the TRUNCATE TABLE statement, the deletion of the record is not recorded. In other words, this means that truncate TABLE is much faster than delete.

MSDN Explanation: Delete all rows in a table without logging a single row delete operation. TRUNCATE table is similar to a DELETE statement without a WHERE clause ; however, TRUNCATE table is faster and uses fewer system and transaction log resources.

TRUNCATE table deletes all rows in the table, but the table structure and its columns, constraints, indexes, and so on, remain unchanged. To delete a table definition and its data, use the DROP table statement. If the table contains an identity column, the counter of the column is reset to the seed value defined by the column. If no seed is defined, the default value of 1 is used. To preserve the identity counter, use DELETE. TRUNCATE TABLE cannot activate a trigger because the operation does not record individual row deletions.