{"id":45810,"date":"2026-07-27T14:07:11","date_gmt":"2026-07-27T12:07:11","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/?p=45810"},"modified":"2026-07-28T10:00:35","modified_gmt":"2026-07-28T08:00:35","slug":"split-sort-collapse-how-sql-server-survives-an-overlapping-unique-update","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/split-sort-collapse-how-sql-server-survives-an-overlapping-unique-update\/","title":{"rendered":"Split, Sort, Collapse: how SQL Server survives an overlapping unique update"},"content":{"rendered":"\n
One table, one single column, 10,000 contiguous values from 1 to 10,000. We shift everyone up by 1:<\/p>\n\n\n
\nUPDATE u SET val = val + 1;\n<\/pre><\/div>\n\n\n
Each new value lands on a value that already exists: 1<\/code> wants to become 2<\/code>, but a 2<\/code> is already there; 2<\/code> wants to become 3<\/code>, but a 3<\/code> is already there\u2026 Every target overlaps a neighboring value. Yet a unique index allows no duplicate, at any instant<\/strong>. How can this update succeed?<\/p>\n\n\n\n
Two tables, one single difference<\/h2>\n\n\n
\n-- Unique column\nCREATE TABLE u (val INT NOT NULL);\nCREATE UNIQUE INDEX ux_u ON u(val);\nINSERT INTO u (val)\n SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL))\n FROM sys.all_objects a CROSS JOIN sys.all_objects b;\n\n-- Same column, without uniqueness\nCREATE TABLE n (val INT NOT NULL);\nINSERT INTO n (val) SELECT val FROM u;\n<\/pre><\/div>\n\n\n
The same UPDATE ... SET val = val + 1<\/code> on each. Yet the two execution plans differ completely: on n<\/code>, a direct update, a single modification operator. On u<\/code>, three unexpected operators appear: Split<\/strong>, Sort<\/strong>, Collapse<\/strong>. That is the entire gap between “any column” and “a unique column”.<\/p>\n\n\n\n<\/figure>\n\n\n\n
Why the naive method doesn’t work<\/h2>\n\n\n\n
Let’s process u<\/code> row by row, in ascending order. First row: 1 \u2192 2<\/code>. But 2<\/code> still exists. Two rows carry the value 2<\/code>: uniqueness is violated immediately. Processing in descending order would get by (10000 \u2192 10001<\/code> first, then 9999 \u2192 10000<\/code>\u2026), but SQL Server can’t bet on the order in which the rows to process are acquired. It needs a method that works regardless of order, and that never lets the same value coexist twice.<\/p>\n\n\n\n
Split, sort, collapse<\/h2>\n\n\n\n
This is the engine’s answer, in three steps. Let’s take five rows with the values 3,4,5,6,7<\/code> and follow each step closely.<\/p>\n\n\n\n
1. Split (the update becomes delete + insert)<\/h3>\n\n\n\n
Modifying the key of a unique index directly is risky: the target value may overlap a value that is present. Split works around the problem by decomposing each update into a deletion<\/strong> of the old value followed by an insertion<\/strong> of the new one. Our 5 rows become 10 operations:<\/p>\n\n\n
\nDELETE 3 (old value of row #0)\nINSERT 4 (new value of row #0)\nDELETE 4 (row #1)\nINSERT 5\nDELETE 5 (row #2)\nINSERT 6\nDELETE 6 (row #3)\nINSERT 7\nDELETE 7 (row #4)\nINSERT 8\n<\/pre><\/div>\n\n\n
A delete can never fail on uniqueness; an insert fails only if the final value is truly a duplicate. We’ve separated the two risks.<\/p>\n\n\n\n
2. Sort (sort to free a value before reoccupying it)<\/h3>\n\n\n\n
The 10 operations are sorted by the index key. Decisive rule: for equal values, the delete comes before the insert<\/strong>.<\/p>\n\n\n
\nDELETE 3\nDELETE 4 -- 4 is freed...\nINSERT 4 -- ...before being reoccupied\nDELETE 5\nINSERT 5\nDELETE 6\nINSERT 6\nDELETE 7\nINSERT 7\nINSERT 8\n<\/pre><\/div>\n\n\n
This is where the overlap is defused. For each value, the old occupant leaves before the new one arrives. At no point do two rows share the same key.<\/p>\n\n\n\n
3. Collapse (reduce the intermediate operations)<\/h3>\n\n\n\n
After sorting, each DELETE k \/ INSERT k<\/code> pair for the same value sits adjacent. Collapse fuses each pair into a single in-place update of the existing index entry, rather than deleting and re-inserting it. The two unpaired ends survive as a real delete and a real insert.<\/p>\n\n\n
\nDELETE 3 (low end, no partner)\nMODIFY 4\nMODIFY 5\nMODIFY 6\nMODIFY 7\nINSERT 8 (high end, no partner)\n<\/pre><\/div>\n\n\n
Does the engine really execute it this way? The transaction log settles it.<\/p>\n\n\n
\nSELECT Operation, Context, COUNT(*) AS n\nFROM sys.fn_dblog(NULL, NULL)\nWHERE AllocUnitName LIKE '%ux_u%'\nGROUP BY Operation, Context\nORDER BY n DESC;\n<\/pre><\/div>\n\n\n
This result is particularly revealing.<\/p>\n\n\n\n
If the five updates had truly been executed as five DELETE<\/code>s followed by five INSERT<\/code>s, we would have expected five deletions and five insertions in the log.<\/p>\n\n\n\n
Collapse is therefore not only a theoretical concept visible in the execution plan. The observations from fn_dblog()<\/code> show that it translates concretely into a massive reduction of physical operations. The intermediate values are merged into simple index row modifications, while only the two ends of the shift survive as a genuine deletion (3<\/code>) and a genuine insertion (8<\/code>).<\/p>\n\n\n\n
4. Visualization of the complete SPLIT-SORT-COLLAPSE process<\/h3>\n\n\n\n
The preceding observations now make it possible to connect the theory to what is actually executed. The following visualization traces the complete sequence of the Split, Sort and Collapse phases, along with the progressive reduction of operations down to the final result observed in the transaction log:<\/p>\n\n\n\n<\/figure>\n\n\n\n
What it costs<\/h2>\n\n\n\n
The Split \/ Sort \/ Collapse<\/code> mechanism lets SQL Server guarantee uniqueness throughout the entire UPDATE, but this safety comes at a significant cost. To measure this impact, we ran exactly the same update on the two 10,000-row tables created earlier, with STATISTICS IO ON<\/code> enabled. The results are unambiguous. On table u, SQL Server performed 40,036<\/strong> logical reads, consumed 266 ms of CPU and 360 ms of elapsed time. On table n, the same operation required only 34<\/strong> logical reads, 15 ms of CPU and 71 ms of elapsed time.<\/p>\n\n\n\n<\/figure>\n\n\n\n
It’s worth noting that this extra cost corresponds mainly to the logical processing imposed by the uniqueness guarantee. Indeed, our analysis with fn_dblog() shows that the engine does not naively delete and then reinsert each row. After Collapse, the intermediate operations are largely merged into simple MODIFY_ROW operations, which limits the physical work actually performed on the index pages. The price to pay is therefore not so much a full rebuild of the index as a substantial amount of preparatory organization that lets SQL Server guarantee that at no instant do two rows simultaneously hold the same unique key.<\/p>\n\n\n\n
A side note about the Halloween Problem<\/h2>\n\n\n\n
This need to read everything before writing anything has a name. In the mid-1970s, Don Chamberlin, Pat Selinger and Morton Astrahan wrote a query meant to give a 10% raise to every employee earning under $25,000. It ran without error but left everyone at exactly $25,000: the engine scanned the salary index upward, and each raised row crossed the threshold, moved back ahead of the scan, and got raised again. They found it on Halloween, and the name stuck after the day, not the nature of the bug (link<\/a>).<\/p>\n\n\n\n
![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-1-1-1024x283.png\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/split-sort-collapse-animated.gif\")
<\/figure>\n\n\n\n![\"\"](\"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/sites\/2\/2026\/07\/image-12-1024x272.png\")
<\/figure>\n\n\n\n