Article now available on my website for those allergic to 𝕏: MERGE Bug with Updates Though a View

Now featuring execution plans

I had Grok 3 proof-read it and suggest (very helpful, actually) improvements.

Should be "It takes the fact that the WHEN MATCHED clause"

You didn't mention that THEN DELETE also fails

Great article otherwise

Also not sure what the *** is going on with this, but it's a different error to the one you got dbfiddle.uk/iAfZ672J

@Charlieface Well, as you know, I regard that as something that should be disallowed in general. It was a bit too much of a detour to work into this article. I wanted it to be quite short and focussed. Perhaps I will revisit it at some stage.

@Charlieface Yes, that's another possible error. Better than an assert. You might remember that error message from sql.kiwi/2024/09/a-small-sample-of-sql-server-chaos

It's just all broken anyway

> seems idealn't

word of the day

@PaulWhite thank you

That 5180 error looks particularly nasty.

@PaulWhite it's even better than the real th𝕏ng

@mustaccio add it to the dictionary

no u

i am not the help

seems helpn't

@HannahVernon yeah, and that's the one I encountered first!

I did actually run DBCC CHECKDB

That's not something you'd really ever want to see. I suppose Microsoft will work on a fix for that issue pretty quickly.

It's always hard to know with these things

Rummaging around in the mind-blowing internals of the MERGE implementation might well be deemed too risky, even for a bug that might do horrible things

they may not prioritize it because of the perceived esoteric nature of using merge against a view with an outer join.

one wonders how many people actually do updates against a view, even without using merge.

You just never know what people are doing w.r.t spacebar heating etc.

this is a good point

On the other hand, it's lain dormant since 2008 so

just the fact that you found it means someone is doing it

Hopefully, SAM is having a great day/night

presumably you didn't just randomly attempt to update a view with an outer join via a merge.

@HannahVernon I did not, no. I was playing around with a design for Erik's problem loading a large heap with NULL placeholders and updating them later

ahhh that makes sense

But it's a fairly well-established design to split tables with optional columns like that 1:1

With a view to present the full table

yeah it makes sense to push sparse columns out to a different table if they are not used all the time.

Erik's problem was forwarded records as the updated rows tried to expand

speaking of sparse columns, I wonder how many people use sparse column sets

🤷🏼‍♂️

I suppose they're great when used for their intended purpose?

it would be mildly interesting to see the statistics Microsoft keeps from their CEIP data gathering around the long tail features.

for the transcript

I wonder if 2025 will see Stretch Tables resurrected

perhaps it just magically uses CoPilot to store all your data in the cloud without asking

What do you think @Erik

@ErikReasonableRatesDarling I actually already replaced the part of the system that this set of unit tests relies on, so it will be able to take advantage of all the work I put in to deal with that in one place. All the XML fragments are in their own table. I have CHECKSUM and HASHBYTES columns for the various uses in the system and manage collisions centrally when a new XML fragment is added.

In addition to saving huge amounts of space through the deduplication, it also cuts down on having extra columns for all those things in any table that needs these fragments. Like there will be some tests that have 3-4 fragments, and all would need the actual XML and the appropriate hash/case sensitive mechanism.

Now they just have the contentID, and different contentID means it's different so, easy peasy.

I think it's about 200,000 "unit" tests that are used to make sure the various inputs and outputs to the Data Warehouse match expected.

It's really 5000 or so human-created tests, but they have to fan through different cardiovascular specialities and versions of the source system to be able to handle, so the run possibilities multiply up.

that's a lot of tests

@PaulWhite i do think sometimes

Stretch Tables V2 confirmed then

@HannahVernon It's a complex space - structured cardiovascular reports are based on these knowledgebases, and the things docs check off are coded and structured, but the data mart is like a flatter, more tabular version amenable to analytics, so it has a lot of rules to get there, and we have to test they are accurate.

But it's computers, right. Everything is just string re-writing anyway. Input is this, output is that. The devil is in between.

Apparently now if Visual Studio is updating (I don't hardly use it, but it has a security vulnerability, and yes, the updater still seems to hang randomly, maybe because I had SSMS open), you can't run SSMS because Visual Studio Shell is being updated.