TRUNCATE TABLE (TSQL Statement)
deletes all records from the specified table.
Syntax
TRUNCATE TABLE <table_qname> [ WITH SHRINK_FILE ]
<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name
Arguments
- <table_qname>
- is the qualified or unqualified name of the table to truncate.
WITH SHRINK_FILE
- with this option, the table files and all indexes files will be physically shrinked.
- this option is not allowed inside a transaction (because rollback would be impossible if data pages have been physically removed).
- this clause doesn’t exist in MS SQL Server.
Remarks
This statement doesn’t change the physical size of the table files on disk. All the pages in these files are marked as empty pages.
To physically shrink the files, use the option WITH SHRINK_FILE
.
But in most cases, there is no reason to do so, because you will certainly insert new records into the table.
Examples
IF OBJECT_ID ('mytable', 'U') IS NOT NULL
DROP TABLE mytable;
CREATE TABLE mytable (a INT IDENTITY NOT NULL, b VARCHAR(50) NULL);
GO
INSERT mytable VALUES ('Row #1'), ('Row #2'), ('Row #3'), ('Row #4');
SELECT *
FROM mytable;
BEGIN TRAN;
PRINT 'Truncate mytable';
TRUNCATE TABLE mytable;
SELECT *
FROM mytable;
ROLLBACK;
PRINT 'After ROLLBACK';
SELECT *
FROM mytable;
The result is:
a |b |
-----------+-----------------------------------+
1|Row #1 |
2|Row #2 |
3|Row #3 |
4|Row #4 |
(4 row(s) affected)
Truncate mytable
a |b |
-----------+-----------------------------------+
(0 row(s) affected)
After ROLLBACK
a |b |
-----------+-----------------------------------+
1|Row #1 |
2|Row #2 |
3|Row #3 |
4|Row #4 |
(4 row(s) affected)