Glad I've already had my first cuppa this morning. Nice one...

Has the table in question been partitioned (or the clustered index)?

If so, then swap out each partition to it's staging table, run the deletes/inserts, then swap back in.

If not, learn...

I would love to. However, that would basically involve a total redesign of the entire database and it's application. Personally, i would recommend scrapping that platform and starting over... the...

I finally figured how to resolve the issue - convert the XML fields to NVARCHAR(MAX), Replace the _x0020_ with an underscore, convert back to XML, run the shred using nodes(),...

Here are a few records drawn from the audit trail table - i needed to mask some columns, due to sensitivity, but the detail is still usable...

the CTE is something like a single use virtual view.

Given that the final select uses a UNION ALL, it will SELECT from the CTE, then...

I have found many who firmly believe a CTE actually creates a volatile table in tempdb and then reads from it.

i cannot figure out where they got that idea from.

That is cool; nice question, thanks Steve

This works really well, too.

Nice question, thanks Steve

Nice, easy one today, thanks Steve

Nice question, thanks Steve.

Been there, done that - best to run that over a weekend when no-one is around...

Interesting question, thanks Steve

Nice question thanks Steve.

i particularly enjoyed the red herrings...

Nice one, thanks Steve

Nice one, thanks Steve