@Alan.B

Yeah, that's how I understood the requirement as well.

Another option:

SELECT DISTINCT
source_database_id=CASE WHEN source_database_id<target_database_id THEN source_database_id ELSE target_database_id END
,target_database_id=CASE WHEN...

The Agent allows running every 10 seconds as a maximum frequency.

At any rate, what you're seeing is basically the big dilemma with implementing such things in SQL Server.

I'm glad that helped.

To be clear, though, it's definitely not perfect, for a couple reasons 🙂

First, and most importantly, that exact query will fail sometimes (i.e.,...

I think you're just seeing another symptom of the oddity/quirk/bug with sys.stats_columns reported in this Connect item: https://connect.microsoft.com/SQLServer/feedback/details/1163126/unexpected-stats-column-id-behavior

If you run DBCC SHOW_STATISTICS for the statistics in question, you...

Something like this should work just to minimize the number of consecutive category_sorts as much as possible:

WITH counted AS (SELECT cnt=COUNT(*) OVER (PARTITION BY category_sort),*...

Here are a couple other query forms that can be used for this sort of problem.

Which one performs best is, well, the usual "It depends..." 🙂

Thanks for providing the data creation scripts and your desired results. That is very helpful!

I do still need the answer to the question in my previous post's EDIT.

As others have indicated multiple times, it would be much more helpful if you could post sample data the way we have in our posts, and actually explain the business logic...

I'm with Luis. I don't know why using a CTE should cause you any issues here.

With that in mind, here's another way that only hits the base table...

It's hard to say much from the little detail posted, honestly.

Any chance we can the deadlock graph or code?

Cheers!

@Alan.B: Thank you sir! Coming from you that's especially high praise 🙂

I absolutely agree; this is a deceptively difficult exercise, and I spent a couple hours last night...

Here's another way (based on the original sample data with two table variables) that uses dynamic TSQL. Of course, all the usual caveats with such things apply (protecting against injection,...