@MartinSmith but that is different (I mean there they are 2 queries with different results are optimized by different indexes. Here the 2 queries are semantically identical). I remember this question: Will altering order of columns in GROUP BY affect performance?, Seems like GROUP BY a,b and GROUP BY b,a yields identical plans but PARTITION BY can't use the same transformation?

@ypercubeᵀᴹ Agreed it's not exactly the same. The solution of adding an order by may well work though. And if it does then could use a derived table with top big number ... order by and then select from that with final order by c.

Oh, I didn't realize what you suggested. You mean add an (internal, in a derived table) order by a,b,c, right? Or change the order by inside the OVER ()?

@Vladimir, it might be helpful to check how all the 4 versions behave if you change the OVER (... ORDER BY c) to OVER (... ORDER BY a,b,c) in all of them.

@ypercubeᵀᴹ an order by in the derived table (like option 2 here dba.stackexchange.com/questions/135764/…)

@ypercubeᵀᴹ, Thanks for suggestion. I tried OVER(... ORDER BY a,b,c). No changes in the plans. Versions 2 and 4 still have extra Sort.

@MartinSmith, wrapping second variant into derived table with TOP and ORDER BY A,B,C seems to do the trick. Still, overall, is it a bug in optimiser? How can versions 3 and 4 produce different plans? The only difference in the query is the order of columns in the SELECT.

I don't know the gory details of why the optimiser behaves in this way. IIRC there are other missing optimisations too where window functions with the same window specification can have an extra sort operation if separated in the select list by one with a different specification.

Yeah, this seems like another missed optimization, and there are plenty of those. The optimizer is written by humans and isn't perfect, but it's certainly getting better. Have you tried under both old and new CE models? This repros for me on SQL Server 2016 RTM (under both CE models) and also on 13.0.1708.0, but have you tried a 2014 build later than 12.0.2000.8, like 12.0.4459? There are occasionally optimizer fixes that quietly make it into SPs and CUs are are fixed as a side effect of other changes.

Thank you for checking it on the latest 2016 @AaronBertrand. I'll try to install the latest service pack and CU for 2014 tomorrow. Should I install a "normal" service pack and CU, or should I look for a special variant for Express? Also, could you remind me how to switch between CE models, please. Our production is on 2008, so I didn't have to worry about the new CE yet.

Get the normal SP & CU - they should be able to find & patch Express. In 2014 you can use the old CE for a query by using OPTION (QUERYTRACEON 9481); (for the new one, use 2312), or for the database by lowering the compat level. In 2016 there is a new database scoped configuration so you can set it at the database level without affecting all the other things touched by compat level.

@AaronBertrand, I installed the latest service pack and CU and tried with both CE models. The miracle didn't happen, the problem is still there. I guess, there is not much we can do here, just something to keep in mind and be careful that PARTITION BY matches the index precisely.

You notice the first Index Scan is Ordered True and the second is not? Maybe I'm missing something but this optimisation looks correct to me. The first query partitions using the order already given by the clustered index. The second query has to re-order to meet the partition specification, hence the "extra" sort

Following some reading I would now phrase it as the result set is identical, but the result sequence of the two functions are different (but repeatable and consistent)

Adding one more message so this room isn't auto-deleted.