Truncating A Replicated Table

On a recent project that I was working on we came across an issue where we needed to replicate several databases on a new server.  Much of the ETL had places where tables were truncated and reloaded.  Normally this is not an issue at all, simply TRUNCATE TABLE and away you go.  This does however cause a problem when you have a replicated database.  According to the Microsoft documentation, which I obviously haven’t read all of, you can’t use TRUNCATE TABLE where the tables are referenced by a foreign key constraint, or participate in an idexed view.  The third situation where it is unavailable is where the table is published using transactional replication or merge replication.  The work around for this, again noted by Microsoft, is to simply use the DELETE statement in place of TRUNCATE TABLE.  Granted most of the time you are not going to be switching and using replication in the middle of a project, but in this case let’s just say there were plenty of ETL changes that needed to be made.  One other note on the difference between the two statements: TRUNCATE TABLE will reset your identity column to a default value of 1, DELETE does not do this.  If you need to remove all records from a table and reset the identity column, as I did, you will need to have a second statement to rest the identity column in addition to the DELETE statement.
You can find some more information about the differences between TRUNCATE TABLE and DELETE on the MSDN page for the TRUNCATE TABLE statement here:http://msdn.microsoft.com/en-us/library/ms177570.aspx  I will also place the list of advantages for TRUNCATE TABLE over DELETE below in case you are interested.
1. Less transaction log space is used.
The DELETE statement removes rows one at a time and records an entry in the transaction log for each deleted row. TRUNCATE TABLE removes the data by deallocating the data pages used to store the table data and records only the page deallocations in the transaction log.
2. Fewer locks are typically used.
When the DELETE statement is executed using a row lock, each row in the table is locked for deletion. TRUNCATE TABLE always locks the table and page but not each row.
3. Without exception, zero pages are left in the table.
After a DELETE statement is executed, the table can still contain empty pages. For example, empty pages in a heap cannot be deallocated without at least an exclusive (LCK_M_X) table lock. If the delete operation does not use a table lock, the table (heap) will contain many empty pages. For indexes, the delete operation can leave empty pages behind, although these pages will be deallocated quickly by a background cleanup process.

About the author

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

2 comments

This site uses Akismet to reduce spam. Learn how your comment data is processed.

  • What did you end up doing in that situation? Did you convert all TRUNCATE statements to DELETE statements or did you use another form of replication? What version of SQL Server were you using?
    I’m looking for a method to replicate a SQL Server 2012 database either in near real-time or at least on a daily basis that allows me to issue TRUNCATE table statements in stored procedures on the publisher database. The subscriber database will be read-only.

  • What did you end up doing in that situation? Did you convert all TRUNCATE statements to DELETE statements or did you use another form of replication? What version of SQL Server were you using?
    I’m looking for a method to replicate a SQL Server 2012 database either in near real-time or at least on a daily basis that allows me to issue TRUNCATE table statements in stored procedures on the publisher database. The subscriber database will be read-only.

Bradley Schacht

Bradley Schacht is a Senior Program Manager on the Microsoft Azure Synapse Analytics team based in Jacksonville, FL. He has worked with Microsoft SQL Server and Azure data services since 2009 as a consultant, trainer, and architect. He has co-authored 4 SQL Server and Power BI books, most recently the Microsoft Power BI Quick Start Guide. Bradley enjoys solving interesting problems and teaching others to use new technology. He frequently presents at community events around the country, is a contributor to sites such as SQLServerCentral.com, and is a member of the Jacksonville SQL Server User Group (JSSUG).

Follow Me