Export (0) Print
Expand All
ABS
ALL
AND
ANY
AVG
bit
COS
COT
DAY
EXP
GO
IN
LEN
LOG
MAX
MIN
NOT
OR
PI
SET
SIN
STR
SUM
TAN
USE
VAR
Expand Minimize

TRUNCATE TABLE

SQL Server 2000

Removes all rows from a table without logging the individual row deletes.

Syntax

TRUNCATE TABLE name

Arguments

name

Is the name of the table to truncate or from which all rows are removed.

Remarks

TRUNCATE TABLE is functionally identical to DELETE statement with no WHERE clause: both remove all rows in the table. But TRUNCATE TABLE is faster and uses fewer system and transaction log resources than DELETE.

The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table's data, and only the page deallocations are recorded in the transaction log.

TRUNCATE TABLE removes all rows from a table, but the table structure and its columns, constraints, indexes and so on remain. The counter used by an identity for new rows is reset to the seed for the column. If you want to retain the identity counter, use DELETE instead. If you want to remove table definition and its data, use the DROP TABLE statement.

You cannot use TRUNCATE TABLE on a table referenced by a FOREIGN KEY constraint; instead, use DELETE statement without a WHERE clause. Because TRUNCATE TABLE is not logged, it cannot activate a trigger.

TRUNCATE TABLE may not be used on tables participating in an indexed view.

Examples

This example removes all data from the authors table.

TRUNCATE TABLE authors
Permissions

TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable.

See Also

DELETE

DROP TABLE

Was this page helpful?
(1500 characters remaining)
Thank you for your feedback
Show:
© 2014 Microsoft