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)
RSQL, a simple alternative to Microsoft SQL Server