The difference between delete and truncate

Original: The difference between delete and truncate

In general, data that is not used is deleted by row, and deletion is usually done with the delete and TRUNCATE commands. For conditionally deleted, basically will use delete, of course, there is no absolute, with TRUNCATE can also be implemented, as long as the "do not need to" delete the data into the new table, and then TRUNCATE the source table, The data is then directed back or renamed directly to the new table.

The following example mainly compares the difference between delete and TRUNCATE in the case of a full table deletion:

First, create a test case: This example uses the AdventureWorks database. Create 3 tables First:

--Heap, that is, no clustered index select * into Sales.salesorderdetail_d from  sales.salesorderdetail--have clustered index select * into Sales.salesorderdetail_j from  sales.salesorderdetail CREATE CLUSTERED INDEX Clustered_salesorderdetail_j on Sales.salesorderdetail_j (salesorderid,salesorderdetailid) go--does not have a clustered index, but has a nonclustered index select * into Sales.salesorderdetail_f From  sales.salesorderdetailcreate nonclustered INDEX nonclustered_salesorderdetail_f on Sales.salesorderdetail_ F (salesorderid,salesorderdetailid) GO

Take a look at the indexes of each table:

Sp_helpindex ' [Sales]. Salesorderdetail_d '; GO

Results:

Sp_helpindex ' [Sales]. Salesorderdetail_j '; GO

Results:

Sp_helpindex ' [Sales]. Salesorderdetail_f '

Results:

Then, use DELETE to clear the three tables:

Use the DBCC showcontig command to view the distribution of data:

DBCC showcontig (' [Sales]. Salesorderdetail_d ') godbcc showcontig (' [Sales]. Salesorderdetail_j ') godbcc showcontig (' [Sales]. Salesorderdetail_f ')

The results are as follows:

As can be seen, the heap table (that is, a table without a clustered index) scans The four pages and a single area, which are empty because they have been deleted. A table with a clustered index, with only 1 pages and 1 extents. A table with a nonclustered index, with a page and 9 extents.

As you can see, tables that do not have a clustered index have a lot of space left behind when they delete data.

Let's look at the TRUNCATE operation:

Similarly, create a table and create the same table using the above built-in statements to ensure contrast consistency:

DROP table Sales.salesorderdetail_dgodrop Table Sales.salesorderdetail_jgodrop table Sales.salesorderdetail_fgo--Heap, That is, there is no clustered index select * into Sales.salesorderdetail_d from  sales.salesorderdetail--Clustered index SELECT * into Sales.salesorderdetail_j from  sales.salesorderdetail  CREATE CLUSTERED INDEX Clustered_salesorderdetail_j on Sales.salesorderdetail_j (salesorderid,salesorderdetailid) go--does not have a clustered index, but has a nonclustered index select * into Sales.salesorderdetail_f From  sales.salesorderdetail CREATE nonclustered INDEX nonclustered_salesorderdetail_f on Sales.salesorderdetail_ F (salesorderid,salesorderdetailid) GO

Then look at the related indexes:

Sp_helpindex ' [Sales]. Salesorderdetail_d '; GO

Results:

Sp_helpindex ' [Sales]. Salesorderdetail_j '; go

Results:

Sp_helpindex ' [Sales]. Salesorderdetail_f '

Results:

Now clear the Operation:

TRUNCATE TABLE [Sales]. Salesorderdetail_dgotruncate TABLE [Sales]. Salesorderdetail_jgotruncate TABLE [Sales]. Salesorderdetail_f

Check the data distribution again:

As you can see,3 tables already have no pages and no extents.

Through the above comparison, the following conclusions can be drawn:

1. Truncate less transaction log space than Delete :

DELETE is a row-by-row operation, and the records are stored in the log file (the log is logged, regardless of any recovery model). While the TRUNCATE operation is for a page operation, in the log, only the action of releasing the page is recorded, not each row is recorded.

2. Truncate locks are usually less used than Delete :

Delete because it is a row is deleted, so the processed rows need to be locked, and is a row lock. TRUNCATE operation because it is a page operation, you only need to apply for page lock or table lock.

3. TRUNCATE empty all pages in the table:

After the DELETE is executed, the table will still have empty pages, but TRUNCATE will clear all. However, TRUNCATE preserves table structure, columns, constraints, indexes, and so on. And after the DELETE , he will be able to clear empty pages in the background.

To better remove space, you can use the following methods:

(1), creating a clustered index in a table

(2), if all data has not been used, then delete the drop TABLEusing TRUNCATE instead of delete.

In addition, the space left by the DELETE operation is reused at insert time. If you feel that these spaces are not good, you can rebuild / Create a clustered index to free up space.

The difference between delete and truncate