SHRINK TABLE (TSQL Statement)

physically shrinks the table and indexes files containing the records of the specified table.

Syntax

SHRINK TABLE <table_qname>

<table_qname> ::= [ database_name. [ schema_name ] . | schema_name. ] table_name

Arguments

<table_qname>
is the qualified or unqualified name of the table to shrink.

Remarks

This statement doesn’t exist in MS SQL Server.

Each table and index stores its data in its own file on disk. These files grow as records are inserted, but their sizes never decrease automatically.

If a lot of deletions have occurred, pages at the end of the files may be empty.

This statement removes the trailing series of empty pages at the end of the files, by physically truncating these files.

This statement is not allowed inside a transaction, because rollback would be impossible if data pages have been physically removed.

This statement is not so useful because usually, records are inserted into tables, and rarely removed. Besides, the first non-empty page encountered starting from the end of the files stops the truncating.

Examples

SHRINK TABLE mytable;
RSQL, a simple alternative to Microsoft SQL Server