This article gives you an insight into the SQL Truncate and SQL Delete commands behavior.
Introduction
In the article Difference between SQL Truncate and SQL Delete statements in SQL Server, we learned the difference between truncate and delete statements. You should go through this article before going further with this article.
In this article, we will look at the following topics.
- Can you Rollback a delete transaction and a truncate command?
- Which one is faster, Truncate or Delete and why?
Rollback a delete transaction
Let’s say you started two separate transactions – delete and truncate. Later, we want to roll back this transaction. We issued a rollback command for this.
Now, a question for you – Does rollback command recovers data for both delete and truncate? You might think we cannot rollback a truncate statement. Let’s explore this practically.
Prepare a database environment
Let’s create a new database [SQLShackDemo] for this article, along with a sample data table:
|
1 2 3 4 5 6 7 8 9 10 11 12 |
CREATE DATABASE SQLShackDemo; GO USE SQLShackDemo; GO CREATE TABLE test (id INT IDENTITY(1, 1), [Name] VARCHAR(10) ); GO INSERT INTO test([Name]) VALUES('SampleData'); GO 10 |
We have 10 records in the test table for demo purposes:
We can use dynamic management view sys.dm_db_database_page_allocations(starting from SQL Server 2012) to list database pages belonging to the SQL table. This DMV takes the following arguments:
- Database id: We can specify database id or use DB_ID() function to pass the current database id
- Object id: This parameter takes object id we want to analyze. It is good to use OBJECT_ID(‘Object’) for automatically supplying this value
- The next two arguments are Index id and partition id. If we specify NULL values, it returns information about all indexes and partitions
- In the last argument, we can specify LIMITED or DETAILED values. Let’s use DETAILED as it gives additional information compared to the LIMITED option output
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
SELECT allocated_page_file_id AS PageFID, allocated_page_page_id AS PagePID, allocated_page_iam_file_id AS IAMFID, allocated_page_iam_page_id AS IAMPID, object_id AS ObjectID, index_id AS IndexID, partition_id AS PartitionNumber, rowset_id AS PartitionID, allocation_unit_type_desc AS iam_chain_type, page_type AS PageType, page_level AS IndexLevel, next_page_file_id AS NextPageFID, next_page_page_id AS NextPagePID, previous_page_file_id AS PrevPageFID, previous_page_page_id AS PrevPagePID FROM sys.dm_db_database_page_allocations(DB_ID(), OBJECT_ID('test'), NULL, NULL, 'DETAILED') WHERE is_allocated = 1; GO |
Alternatively, we can use DBCC IND command as well and it returns almost the similar output:
|
1 |
DBCC IND('SQLShackDemo','test',1) |
In the output, we can see two pages with PagePID 94 and 256:
- PagePID 94: It is an IAM (Index Allocation Map) page. The IAM page has NULL values for IAMFID and IAMPID columns. PageType 10 denotes IAM page
- PagePID 256: It is a data page. PageType 1 denotes a data page
Now, let’s begin a delete transaction using the begin transaction statement:
|
1 2 3 4 |
USE SQLShackDemo; GO BEGIN TRANSACTION; DELETE FROM [dbo].[test]; |
Since we did not specify the WHERE clause in the delete statement, it removes all ten rows from the test table:
Do not commit the transaction as of now. We use the undocumented function fn_dblog to read transaction logs. You can refer to this article, How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog to go through this command in detail.
Note: You should be careful using this function in the production environment. Here, we are using it for demonstration purposes.
In the same query window with existing Begin SQL transaction, execute the following command of fn_dblog. This query filters the transaction logs for delete transaction ( defined as test1 in begin transaction statement):
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
USE SQLShackDemo; GO CHECKPOINT; BEGIN TRANSACTION Test1; DELETE FROM [dbo].[test]; SELECT [Current LSN], [transaction ID] tranID, [end time] endTime, AllocUnitId, Description, operation, Context FROM ::fn_dbLog(NULL, NULL) WHERE [Transaction ID] IN ( SELECT [Transaction ID] FROM fn_dbLog(NULL, NULL) WHERE [Transaction Name] = 'Test1' ); |
SQL delete statement removes individual row and logs entry for each row in the transaction log. We removed 10 rows from the test table, and output also reflects LOB_DELETE_ROWS entry for an individual row. This LOP_DELETE_ROWS operation occurs for the delete statement. We can also see a total of 11 rows in the output of fn_dblog for this delete operation:
Execute the select statement in the same transaction, and it returns zero in the output:
|
1 |
Select count(*) from [dbo].[test]; |
Now, issue a rollback statement and check for the number of rows in the test table. It returns 10 rows in the output. It shows you can rollback a delete transaction:
|
1 2 |
Rollback transaction Select count(*) from [dbo].[test]; |
Rollback a truncate transaction
Now, we want to check whether we can rollback a truncate statement or not. For this demo, drop the test table and recreate it with the same 10 rows from the script shared above.
Once we have an environment ready, start a new transaction and truncate table from the following script:
|
1 2 3 4 |
USE SQLShackDemo; GO BEGIN TRANSACTION; TRUNCATE TABLE [dbo].[test]; |
In the following query, we started a transaction T1 and filtered output of fn_dblog for that particular transaction:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
USE SQLShackDemo; GO CHECKPOINT Go BEGIN TRANSACTION T1; TRUNCATE TABLE [dbo].[test]; SELECT [Current LSN], [transaction ID] tranID, [end time] endTime, AllocUnitId, Description, operation, Context FROM ::fn_dbLog(NULL, NULL) WHERE [Transaction ID] IN ( SELECT [Transaction ID] FROM fn_dbLog(NULL, NULL) WHERE [Transaction Name] = 'T1' ); |
For SQL truncate, we do not get an entry for individual rows in the transaction log. It deallocates the extent, as shown below:
Let’s issue rollback transaction command and check records in a test table. We get our records back. Yes, it proves that we can rollback a SQL Truncate statement as well:
SQL Server minimizes logging for truncate. To prove this point, insert more records in the test table using the following query:
|
1 2 3 |
INSERT INTO test([Name]) VALUES('SampleData'); GO 90 |
We have 100 records in the test table. Now, check the transaction log records count for both truncate and delete operations using scripts specified above.
-
Transaction log records for delete: 101
-
Transaction log records for truncate: 12
From the above screenshots, we can verify that as the record count increases in a table, transaction log record count also increases for delete statement. It does not change much for the truncate statement. Hence,we can see, it takes more time to delete records in comparison with truncate for a huge table. If we wish to remove all records from a table, we should use truncate because it is fast and minimize transaction log growth.
Conclusion
In this article, we explored internals of the SQL Truncate and SQL Delete statement. It also shows the reason for a fasater execution of the truncate statement in comparison with the delete statement. You should use commands as per your requirements.
I am the author of the book "DP-300 Administering Relational Database on Microsoft Azure". I published more than 650 technical articles on MSSQLTips, SQLShack, Quest, CodingSight, and SeveralNines.
I am the creator of one of the biggest free online collections of articles on a single topic, with his 50-part series on SQL Server Always On Availability Groups.
Based on my contribution to the SQL Server community, I have been recognized as the prestigious Best Author of the Year continuously in 2019, 2020, and 2021 (2nd Rank) at SQLShack and the MSSQLTIPS champions award in 2020.
Personal Blog: https://www.dbblogger.com
I am always interested in new challenges so if you need consulting help, reach me at rajendra.gupta16@gmail.com
View all posts by Rajendra Gupta
- How to install PostgreSQL on Ubuntu - July 13, 2023
- How to use the CROSSTAB function in PostgreSQL - February 17, 2023
- Learn the PostgreSQL COALESCE command - January 19, 2023