Ok, I confirmed that the problem is ties throwing off the relationship between the ascending and descending row numbers.

It seems that method is just not safe because of the...

I know it would return rows. The suspicion is that ties would mess with the relationship between the row number sequences and we would see that no rows had the...

It's probably due to some funky behavior with the two ROW_NUMBER() functions and ties in the data set (I remember there being some funkiness there).

What's the result of the following...

That's the same date range covered by the test data, so I'm still not 100% clear. You're seeing correct results when run against the test data, but incorrect results when...

Do you mean the result of the median query I posted with that sample data is missing rows for some YearMonths?

I see...

Your table definition lacked datatypes, and the INSERTs were into TEST, not #TEST, but I cleaned that up to get the following for the test data:

CREATE TABLE #TEST

...

Nice references for a real join type; the actual topic of conversation, the mythical LEFT INNER JOIN, will prove more elusive. 🙂

So, it would simultaneously return only matching rows from both inputs, and yet all rows from the left input. Maybe a .gif with the intersection shaded, and the non-intersecting portion...

A cross tab query should work well for this sort of thing.

Something like this (using just a subset of the columns in the real table for sample data):

CREATE TABLE #CustomStudent

...

I can sympathize....

Well, it's not quite undocumented; they just explicitly disallowed this.

From https://msdn.microsoft.com/en-us/library/ms174979.aspx:

It...

+1

Should've thought to mention that 🙂

The short answer: The constraint on the swap_in table is not correctly aligned with partition 3.

The long answer:

The constraint allows for a date equal to '2008-02-01' (really '2008-02-01 00:00:00'), while...

You've only specified values in the CASE for the conditions where the value is NULL or '0'. You would need to add an ELSE to grab the rest of the...

Shrink commands executed on the primary will get run on the mirror as well, so you shouldn't have to do anything special for the mirrored DB.

Cheers!