The TRUNCATE TABLE command in SQL Server

The TRUNCATE TABLE statement is used to delete all records from a table in SQL Server. This command works similarly to the DELETE command but without the WHERE clause.

Syntax of the TRUNCATE TABLE command in SQL Server

 TRUNCATE TABLE [ten _CSDL.] [ten_schema.] ten_bang 
 [ WITH (PARTITIONS (so_phanvung 
  | so _phanvung TO so _p  | so _phanvung TO so _p hanvung)]; 

Variable name or variable value

ten_CSDL

It depends on you. If specified, this is the name of the database.

ten_scheme

Option. This is the name of the schema (translating as schema or namespace) that the table belongs to.

ten_bang

The table you want to delete the record.

WITH (PARTITIONS (so_phanvung | so _phanvung TO so _phanvung)

Optional and can only be used with a partition table. If specified, so_phanvung is the number of the region you want to delete in this table. To delete multiple zones, use comma to separate values ​​or range of numerical partition values. If you use this clause with a non-partitioned table, SQL Server will report an error. This feature is not available in all versions of SQL Server.

Note

1. If you delete all records in the table, the counter at every indentity column will be repeated from the beginning.
2. Cannot delete all records in the table referenced by Foreign Key.
3. Before deleting the entire record in the table, it must have priority as ALTER TABLE.

For example

In SQL Server, deleting the entire record is the fastest way if you don't have to retrieve data. If the table is TRUNCATE, deleting rows will not be recorded so it cannot be returned to the previous operation. TRUNCATE is also easier than deleting a table with the DROP command or creating a new one with CREATE. See the example below.

 TRUNCATE TABLE; 

This example will delete all records in the table.

The above command is equivalent to the DELETE command below.

 DELETE FROM nhanvien; 

Both of these commands delete all data in the table. The difference is that with the DELETE command you can return to the operation before deleting if you want to remain with TRUNCATE.

Let's see another example with the name of the database.

 TRUNCATE TABLE totn.danhba; 

This example deletes all list entries in the database named totn.

For example with partitions

If you want to delete a 1 or 1 region record, use the TRUNCATE TABLE command with the WITH PARTITIONS clause.

TRUNCATE TABLE nhanvien
WITH (PARTITIONS (1 TO 5, 7));

In this example, the table is a partition table and the TRUNCATE TABLE statement will delete regional records from 1 to 5 and zone 7 on this table.

Frequently asked questions

Q: Can I back up the database to the old state (rollback) after executing the TRUNCATE TABLE command in SQL Server?

Answer: The TRUNCATE TABLE command can be rolled back using the transaction. Example as below.

 CREATE T ABLE bang_test (cot1 int); 


 INSERT INTO bang_test VALUES (1); 
 INSERT INTO bang_test VALUES (2); 
 
 INSERT INTO bang_test VALUES (3); 
 

 -- Tạo chuyển tiếp 
 BEGIN TRAN; 
 

 -- Xóa bản ghi trong bảng 
 TRUNCATE TABLE dbo.bảng_test; 
 

 -- Rollback bảng đã xóa bản ghi 
 ROLLBACK; 
 

 SELECT * FROM bang_ test; 

The SELECT statement above will return the results of the records below.

 cot1 
 ---------- 
 1 
 2 
 3 

Thus, the database state has been restored to the old state and the 3 records remain on the state_test.

Previous article: DELETE command in SQL Server

The following article: Conditions EXISTS in SQL Server