If you know that all rows in table1 come before rows in table2, you can go ahead and assign the ranking as part of the load of the table. ...

The best way to do this is to cluster the table on ( id, iDate ); that is, the clustered index on the table should be on those columns.  And,...

Weird.  When a Merge Join is appropriate, it should typically be the fastest type of join.  Make sure the query actually does have good row estimates.  Also, if you rebuilt...

I'd be inclined to just do this:


SELECT @RunDay = CONVERT(CHAR(2), SomeDate, 1)
FROM #TestTable;

Interesting on the smaller ones.  Probably just SQL generating a non-optimal query plan.  You can probably resolve that; worst case, you'd have to use a "hint" to force SQL to...

That makes sense, sorry about the rant.  It's just that I've seen far too many database "designs" where every table got an identity clustering key slapped on it before the...

It would very likely help performance.  Certainly if:

1) you created a nonclustered columnstore index that covered the query entirely.
Or
2) the total rows you're retrieving were not a...