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.

Truncating A Replicated Table

Comments

More from this blog

Azure Data Studio is dead...now what?

Working with Fabric shortcuts in PySpark

Gathering useful notebook and environment details at runtime

Using sempy to get SQL query CU cost from the Fabric Capacity Metrics app

List Parameters for a Python Function

Command Palette

Comments

More from this blog