So i was doing my morning blog reading and stumbled upon this fun exercise:
https://www.erikdarlingdata.com/sql-server/lets-design-an-index-together-part-3/
Here is the query in question from the article and the index he proposes.
SELECT TOP (5000)
p.LastActivityDate,
p.PostTypeId,
p.Score,
p.ViewCount
FROM dbo.Posts AS p
WHERE p.PostTypeId = 1
AND p.LastActivityDate >= '20110101'
ORDER BY p.Score DESC;
CREATE INDEX whatever
ON dbo.Posts(PostTypeId, Score DESC, LastActivityDate)
INCLUDE(ViewCount) WITH (DROP_EXISTING = ON);
Pretty fun build and index and try and tune it accordingly. However, I had been previously under the possible misunderstanding that index key order matters and that certain WHERE clauses might not use certain indexes when the index key order doesnt match the query. Meaning, that my lack of experience with the particular scenario listed out, my assumed thought would be that this query wouldnt use this index because Score is in the middle of the index key definition but not in the query's where clause.
Does ORDER BY columns get evaluated when the optimizer decides what index to use and as long as the WHERE clause columns and ORDER by columns are in the index definition then itll use it?
I guess my question is more about how the optimizer evaluates indexes in regards to WHERE clauses and ORDER BY clauses.