Indexing Fundamentals: Indexing for ORDER BY

The below can be used as a guideline for creating Indexes for queries having ORDER BY clause.

  • ORDER BY on clm3 in a query having 2 equality searches on clm1 and clm2
    • The column in ORDER BY is added into the Index keys, coming last.
  • ORDER BY with inequality searches
    • We can seek into the column with equality search. (It has to be first in WHERE clause and also in Index Key)
    • Then scan all the rows for matching the inequality
    • And the last Order By, it has to Sort the results because it doesn’t matter if it’s in clustering key as: The order inside an index works on the order of keys. Sorted on first column, then on second and then on third and so on
    • If there is clustered index on the same column as in order by, SQL server will not require Sort operator if the Where has equality but if it has inequality in the Where clause than there will be Sort operator in the execution plan
  • Indexes help by pre-sorting rows to prep them.
    • Where (Equality)
    • Order By
    • Group By, From, Join, CTEs
  • ORDER BY with TOP
    • The creation of Index will depend upon the query itself. Like for a query with TOP Index can be created on,
      • ORDER BY
      • WHERE (Inequality)
      • ….
  • When having equality in WHERE along with ORDER BY, then Create index with WHERE and ORDER BY column coming last
  • But when there is inequality in WHERE, then the sequence of columns in your index will greatly depend upon the selectivity of the values.
  • Always  check for the selectivity (using count(*)) for the WHERE clause.
  • When we using TOP with ORDER BY some column then it might help to have an index on the column in ORDER BY (If nothing else is selective enough)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s