There are many myths and misconceptions regarding TRUNCATE in SQL Server. I think this is heavily influenced by how other Relational Database Management Systems (RDBMS) handle TRUNCATE.
Myth one: You can’t rollback TRUNCATE.
You can actually ROLLBACK a TRUNCATE statement! (If it’s SQL Server.) Go ahead and create a table in a dev environment and give it a try!
CREATE TABLE dbo.TruncateVSDelete_RandomstringofStuff (ID INT); INSERT INTO dbo.TruncateVSDelete_RandomstringofStuff VALUES (1); BEGIN TRANSACTION SELECT * FROM dbo.TruncateVSDelete_RandomstringofStuff; TRUNCATE TABLE dbo.TruncateVSDelete_RandomstringofStuff; SELECT * FROM dbo.TruncateVSDelete_RandomstringofStuff; ROLLBACK TRANSACTION SELECT * FROM dbo.TruncateVSDelete_RandomstringofStuff; DROP TABLE dbo.TruncateVSDelete_RandomstringofStuff;
In addition to being able to ROLLBACK a TRUNCATE statement, it doesn’t matter whether your database is in FULL, SIMPLE, or BULK-LOGGED, you can ROLLBACK regardless!
SQL Server can issue the DELETE statement in parallel, rolling back is another story and takes much longer to complete. This because when you ROLLBACK, SQL Server has to rebuild the data without parallelism. So if you had six CPU’s working for one minute to DELETE, it would take roughly six minutes to ROLLBACK.
Myth Two: DELETE and TRUNCATE do the same thing.
TRUNCATE logs what pages or extents are deallocated from the statement whereas DELETE logs each row that is removed. As seen above, the logging of the pages and extents allows the ability to roll-back and roll-forward the transaction.
This behavior also requires different permissions. TRUNCATE requires you to have ALTER TABLE permissions (a Data Definition Language / DDL command) set whereas DELETE requires DELETE permissions (A Data Manipulation Language / DML command). If you are trying to use TRIGGERS to capture any information on TRUNCATE statements, you’ll be sad to know that you cannot capture TRUNCATE statements with them because they are DDL commands.
TRUNCATE is significantly more efficient at removing data than DELETE, but you cannot control how much of the data from the page or extent is removed. TRUNCATE is an all or nothing feature.
DELETE allows you to append a where clause letting you filter the data that is removed. You can even create more complex query operations with joins and other logic. (Not typically advised for performance reasons.)
TRUNCATE also will re-seed your IDENTITY column! This can be good or bad, it just depends on your business needs. This means if you had a primary key on a table, that key would begin incrementing again at 1 instead of wherever it’s original value was. You can achieve this behavior by using re-seed functions in addition to DELETE, but TRUNCATE is more efficient if you can use it instead.
Myth three: If it’s a table, you can TRUNCATE it.
This one falls into the “it depends” category, just because you have a table doesn’t mean you can TRUNCATE it. There are numerous catches and instances where you can’t TRUNCATE a table.
Foreign Key Constraints will prevent you from truncating your table. This makes sense when you remember that TRUNCATE is a minimally logged function. If TRUNCATE were able to affect tables with Foreign Key Constraints, it would need to check those constraints at each row logged. We aren’t logging rows though, we are trying to deallocate pages and extents, so that’s why you’ll be presented an error message when trying to TRUNCATE a table with Foreign Key Constraints.
If your table is a table variable, you cannot deallocate any pages from this table. Therefore, you cannot TRUNCATE a table variable. You can DROP the table variable though, which is similar. You can also DELETE from a table variable if you needed to.
Myth Four: You can’t determine how many rows are affected by Truncate.
Ok, this one is kind of a “gotcha” and it does require some development work. A little while back I wrote an article about how to capture this kind of information. The “too long didn’t read” version is having a trigger for Insert / Delete on the table you want to record records for and creating a logging table to keep track of the flow of Inserts / Deletes. You can use that for a historical view of how many records used to exist in the table before it was truncated.
