4.4. TRUNCATE TABLE

The TRUNCATE TABLE command removes all rows from a table without logging each row deleted. No logging means it runs faster than DELETE FROM tablename. It also resets IDENTITY column to its initial (seed) value. TRUNCATE TABLE cannot be used on parent table of Foreign Key constraint.

4.4.1. TRUNCATE TABLE Syntax

The syntax of TRUNCATE TABLE and some examples of its use are shown here. In addition, Table 4-3 offers a comparison between DELETE FROM table and TRUNCATE TABLE table.

Syntax
TRUNCATE TABLE tablename

Example:

SQL
												SELECT COUNT(*) NumRows FROM products
 
Result
NumRows-----------65
SQL
												TRUNCATE TABLE  products
												-- Similar effect to:    DELETE  FROM  products
												-- But not logged and resets IDENTITY.
 
Result
The command(s) ...

Get Transact-SQL Desk Reference now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial