TRUNCATE with PARTITION
TRUNCATE with PARTITION is a SQL statement in the DML Statements category. SQL Server: truncates specific partitions instead of the entire table. Faster than DELETE for partition cleanup. The syntax is TRUNCATE TABLE table_name WITH (PARTITIONS (partition_number));. It returns partition truncated. A typical example: -- Truncate specific partition: TRUNCATE TABLE sales_data WITH (PARTITIONS (4)); -- Only partition 4 is emptied -- Multiple partitions: TRUNCATE TABLE sales_data WITH (PARTITIONS (2, 4, 6)); -- Truncate a range: TRUNCATE TABLE sales_data WITH (PARTITIONS (1 TO 3)); -- Benefits vs DELETE: -- - Minimal logging (much faster) -- - Resets partition space -- - No lock escalation -- - Cannot use WHERE (partition-based only) A close relative is INSERT INTO, which inserts new rows into a table. A close relative is UPDATE, which modifies existing rows in a table. Always use WHERE to avoid updating all rows. A close relative is DELETE, which removes rows from a table. Always use WHERE to avoid deleting all rows.