TRUNCATE TABLE – DDL vs DML – deep dive in operations and practical tips

Recently, I stumbled across a LinkedIn post about truncate vs. delete, just like many others out there. Typically, random members post simple SQL Server tips, nicely packaged in graphics and kept general. Although for beginners this is absolutely legitimate – since these simple entry points can help many newcomers understand and provide a great starting point – nevertheless, the fact that the same topics then go viral across various accounts is clearly another matter entirely.

Anyway. If you’re a bit further along in the topic and already know that the pronunciation isn’t „SQL“ but „sequel“ 😊, you should fundamentally question these posts, as they mostly generalize.

This was also the case in a post about the top 3 SQL mistakes that „everyone“ makes. And here we are, getting to the point.

LinkedIn

The same happened with Vedran Kesegic, who gave the hint that TRUNCATE is apparently rollback-capable when encapsulated in a transaction. Then Carsten Saastamoinen-Jakobsen jumped in and things got wild. So wild that the whole topic hooked me so much that I did a deep dive into it. Furthermore, so much knowledge dropped in this exchange that it would be a shame if it disappeared in a comment section.

What to expect here

I compared the exchange and looked at the core statements. I also texted separately with both of them a bit more, and Carsten gave me excerpts from one of his publications where the topic is covered technically.

Technically speaking, both agree, even if they don’t say it 🤣. But what was really wild in the end was the question:

I can say it upfront. This question is an philosophical one. I was able to form an opinion based on my research, more on that later, but basically it’s up to everyone to decide for themselves.

The Discussion – DML or DDL ?

Vedran Kesegic

Core Position: TRUNCATE is DDL

„TRUNCATE modifies internal system tables sys.sysallocunits and sys.sysrowsets. DELETE modifies indexes and stats“

„DML writes data (rows in user tables), while DDL writes metadata (rows in system tables, data that describes other/user objects). Truncate happens to write metadata (system tables) and thus is classified as DDL.“

Technical Arguments

• Uses SCH-M (Schema Modification) lock
• Modifies system metadata tables
• Microsoft documentation classifies it as DDL
• Logs page deallocations, not row deletions

Key Statement

„TRUNCATE modifies internal system tables sys.sysallocunits and sys.sysrowsets. DELETE modifies indexes and stats – see attached“

Carsten Saastamoinen-Jakobsen

Core Position: TRUNCATE is DML

„It is not an argument to call TRUNCATE a DDL statement because a certain lock is used! When statements are categorized, it must be based on what happens and not how a given database system solves the task.“

„We may have to recognize that a previous error categorization should not continue to call TRUNCATE a DDL, but recognize that it is a DML.“

Logical Arguments

• Data is deleted, structure is preserved
• No table definition changes occur
• Follows semantic rules of data manipulation
• Can delete only some partitions (not all data)

Key Statement

„I say that MS is right in the documentations because they are not telling that TRUNCATE is as DDL, because they know that TRUNCATE is a DML!“

NOTE Gabriel
I think this is really a bit questionable because the documentation is absolutely clear on this point –> DDL (microsoft.com)

Quote from publication

„As of version 2016, it is possible to delete only some of the data in a table with TRUNCATE, so it is even more cor­rect to call it manipulation than definition, because it is changing some of the data but not changing the structure.“

Points of Agreement

• TRUNCATE can be rolled back in a transaction
  • general demo at sqlauthority.com
  • deep dive demo sqlshack.com
• TRUNCATE is a logged operation
• TRUNCATE is faster than DELETE for large datasets
• Both understand the technical implementation

Core Disagreement

• Classification criteria
  • Implementation details
• Authority
  • Vendor documentation (Vedran) vs. Logical reasoning (Carsten)
• Perspective
  • How it works internally (Vedran) vs. What it does functionally (Carsten)

My 50 Cent

So as you can see, technically it fits, but how do you view the whole thing? Maybe it’s good that I’m not preloaded with as much knowledge as both of them because from my perspective it’s quite clear: DDL!!

Contrary to Carsten’s statement that no system tables are changed, Vedran’s statement is absolutely correct. See why…

… and of course because MS says it’s DDL 😊 ( microsoft.com )

And partitioning is DML… I don’t know, here too it’s at least system columns that define the partitioning.

How TRUNCATE works

I’d like to say I’ll describe it simply, but that’s just my normal description. The topic is really complex ✌️

1 – TRUNCATE Command Initiated

TRUNCATE TABLE dbo.MyTable;

• First action: Acquire SCH-M (Schema Modification) lock on entire table
• Effect: Table is completely locked – no reads, no writes, nothing

2 – Identify Extents

• DEEP DIVE Extents –> microsoft.com
• SQL Server reads IAM pages
• Lists all extents belonging to the table
• Still holding: SCH-M lock on table

3- Lock Individual Extents

• Places X (Exclusive) locks on each extent
• These are regular data locks, NOT schema locks
• Now holding:
  • SCH-M lock on table (prevents access)
  • X locks on extents (marks them for deallocation)

4 – Log the Operation

• Writes to transaction log: „Deallocate extents 8:8, 8:16, etc.“
• Still holding: Both lock types

5a – If COMMIT

• Updates IAM – marks extents as free
• Releases all X locks on extents
• Releases SCH-M lock on table
• Table is accessible again

5b – If ROLLBACK

• Discards deallocation markers
• Releases all X locks on extents
• Releases SCH-M lock on table
• Everything unchanged

Why Two Different Lock Types?

SCH-M Lock:

• „Nobody touch this table AT ALL“
• Typical for DDL operations
• Prevents even metadata queries

X Locks on Extents:

• „These specific data blocks are being modified“
• Typical for data operations
• Ensures extents aren’t reused until commit/rollback by other sessions

My humble Opinion

Given this knowledge, the case is actually clear for me, but how do you see it? Indeed, here no user data is being manipulated; instead, system-level assignments are being adjusted. Consequently, I’ll allow myself, despite my new half-knowledge, to make the following comparison:

„I also don’t say I’m deleting text in a Word document when I quick format the disk and only delete the block allocation.“

Yes, I think that’s a nice comparison… Case closed, now a few technically interesting aspects and solutions

Technical Part, Hacks and How2

… practical, not technical 😉

TRUNCATE with Survivor Table (Temporary Table)

Data that is still required is simply moved to a temporary table and retrieved after TRUNCATE

-- Step 1: Create temp table with rows to keep
	SELECT * INTO #SurvivorRows 
	FROM 
		MyTable 
	WHERE
		DateColumn >= '2024-01-01'

-- Step 2: Truncate the entire table
	TRUNCATE TABLE MyTable

-- Step 3: Re-insert survivor rows
	INSERT INTO MyTable
	SELECT * FROM #SurvivorRows

-- Step 4: Clean up
	DROP TABLE #SurvivorRows

TRUNCATE with PARTITION option (2016++)

Since SQL Server 2016 (every edition) it is possible to specify partitions or ranges of partitions

-- Truncate specific partitions
	TRUNCATE TABLE MyPartitionedTable 
	     WITH (PARTITIONS (2, 4 TO 6))

-- Check partition status
	SELECT 
		 partition_number
		,rows 
	FROM
		sys.partitions 
	WHERE 
		OBJECT_NAME(OBJECT_ID) = 'MyPartitionedTable'

TRUNCATE is always a transaction…

…. and must be considered during RESTORING. Carsten illustrated this whole thing very nicely in his publication. I’m just describing it, that has to suffice at this point 😝

Even though we only write TRUNCATE TABLE, SQL Server still uses BEGIN and COMMIT TRANSACTION, which of course is recorded in the TLog. Accordingly, during a POINT IN TIME RECOVERY, the data in the table remains available until the TLog with the corresponding TRANSACTION is restored, or the time of the TRUNCATE transaction has been exceeded.

So you can also restore the data 👌

Continuously read Transaction log file data with fn_dblog and fn_dump_dblog

Vedran wrote in one of his posts that there’s an undocumented function where you can read log records at runtime. There’s also a very cool article about this on sqlshack.com

NOT recommended for production but only for test / education.

How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog

Here we’ll discuss how to read SQL Server transaction logs and provide and insight into how the workload is being captured in the transaction log files.

Finally

Let’s get to my initially mentioned point that all these SQL Server tips from randoms should be questioned. Because the statement that truncate is not rollback safe is simply wrong. The path to this realization was long for me, initiated by the conversation between Vedran and Carsten. I really learned a lot left and right, which I also wanted to record here again for posterity.

In the end, it doesn’t matter whether it’s DDL or DML. In the general SQL Server standard, for example, it’s described as DML, most RDBMSs in dialect with DDL. We’ll just agree on DDL and that’s it. Much more exciting is what happens under the hood.

Best comment from Vedran 🤣

„Or you are trying to argue with SQL Certified Master about SQL? Good luck Mr :)“

Links from the text

Transact-SQL statements – SQL Server

Transact-SQL statements

TRUNCATE TABLE (Transact-SQL) – SQL Server

TRUNCATE TABLE removes all rows from a table or specified partitions of a table.

SQL SERVER – Rollback TRUNCATE Command in Transaction – SQL Authority with Pinal Dave

This is a very common concept that truncate cannot be rolled back. Let us learn in today’s blog post that Rollback TRUNCATE is possible.

The internals of SQL Truncate and SQL Delete statements

This article gives you an insight into the SQL truncate and SQL delete commands behavior.

How to continuously read Transaction log file data directly in a SQL Server database with fn_dblog and fn_dump_dblog

Here we’ll discuss how to read SQL Server transaction logs and provide and insight into how the workload is being captured in the transaction log files.

Truncate (SQL) – Wikipedia

In SQL, the TRUNCATE TABLE statement is a data manipulation language (DML)[1] operation that deletes all rows of a table without causing a triggered action. The result of this operation quickly removes all data from a table, typically bypassing a number of integrity enforcing mechanisms. It was officially introduced in the SQL:2008 standard, as the optional feature F200, “TRUNCATE TABLE statement”.

Pages and Extents Architecture Guide – SQL Server

This guide describes the data structures that are used to manage pages and extents in all versions of SQL Server.

Full Conversation