The TRUNCATE TABLE statement is another way to delete all rows from a table. Unlike the DELETE statement, SQL Server does not log individual row deletion in a transaction log. Therefore, this operation is not recoverable because when you run the TRUNCATE TABLE statement, SQL Server just logs page deallocations that occur as a result of this operation. The TRUNCATE TABLE statement is much faster when compared to the DELETE statement, with no WHERE clause, because it uses fewer system and database transaction log resources.

You cannot use the TRUNCATE TABLE statement on tables that are referenced by a foreign key constraint, included in an indexed view, or published for transactional or merge replication. In such a situation, you are required to use the DELETE statement without a WHERE clause to remove all rows.

To execute the TRUNCATE TABLE statement, the user must have at least an ALTER permission assigned on the target table. The syntax for this command is as follows:

TRUNCATE TABLE [[database.]owner.]table_name

The following is the example of the TRUNCATE TABLE statement, which will delete all records from a dbo.CustomProduct table:

USE [AdventureWorks2012];
GO

TRUNCATE TABLE [dbo].[CustomProducts];
GO