It can do them. One limitation is doing them in a parallel plan. Other times it may choose not to. Query optimization is a lot like playing the lottery.
Why is there no sort operator in the Query Plan at 07:55? Of course it uses the Index on the descending reputation first, but it should still need to order by the ascending Id column - except reputation is unique (in the higher ranges), but even with statistics the SQL server can't know/guarantee this, so it would need to sort. A possible reason would, if the ID is the clustered Index and SQL server guarantees, that rows would always be implizit ordered by Reputation DESC + Id ASC, even when the Id is not specified in the CREATE INDEX (the columns of clustered Index will always be included, but I in my opinion unsorted.)
Because when there’s an equality predicate which guarantees sort ordering. I’m mobile at the moment but if you search my channel for something like index sort you should find videos about it.
Good video, thanks!
Beautiful stuff.
Thank you kindly 😃
Great video, thanks.
Why does the sort order in the index matter here? Can't SQL Server do backwards scans. When can and can't it do one?
It can do them. One limitation is doing them in a parallel plan. Other times it may choose not to. Query optimization is a lot like playing the lottery.
Why is there no sort operator in the Query Plan at 07:55? Of course it uses the Index on the descending reputation first, but it should still need to order by the ascending Id column - except reputation is unique (in the higher ranges), but even with statistics the SQL server can't know/guarantee this, so it would need to sort.
A possible reason would, if the ID is the clustered Index and SQL server guarantees, that rows would always be implizit ordered by Reputation DESC + Id ASC, even when the Id is not specified in the CREATE INDEX (the columns of clustered Index will always be included, but I in my opinion unsorted.)
Because when there’s an equality predicate which guarantees sort ordering. I’m mobile at the moment but if you search my channel for something like index sort you should find videos about it.