Indexing Fundamentals: Indexing for the WHERE Clause

Below can be used as a basic guideline for creating on WHERE clause.

Indexing the WHERE clause

  • SQL Server has to seek on the first column inside the index
  • What it means is that the sequence of columns in your Where Clause should be same as in Create Index Clause
  • Index recommendation is most focused on Where clause of your query and majority of time makes suggestions based on that only
  • Clustering key is always there in the indexes and so there is no need to declare them inside the Create index
  • Create index on column in Where and “include” the non-clustering key columns
  • When Visualization query No Sort operator and no seek.

WHERE with 2 operators

  • Sequence of Columns in Where and in Cluster Index
    • Selective doesn’t mean how unique your column values are
  • A seek in execution plan doesn’t mean everything in the Where is being Seek. Check the Seek Operator properties to know what all has been seek and rest is scanned.
  • The sequence of columns in Where and sequence of columns in Index can matter check for logical reads by using Query hints and forcing the query to use a specific index

WHERE with inequality searches

  • In case of inequality the sequence matters in index definition
  • It could be better to have equality first in WHERE clause
  • Selectivity is not just about how many unique values there are for a column but how you run your queries, Lastaccessdate can be fairly unique but there is very small possibility of having a query with some specific time but generally such columns are used along with range.

For equality searches key field order doesn’t matter

For inequality searches the order matters a lot, make sure first is the one which brings minimum rows

Check the Seek operator for any hidden scans

  • Order of columns doesn’t matter in Include part of indexes
  • Like is an inequality sign
  • SQL Server always scan the smallest copy of the table which can fulfill the query
  • Sp_blitzIndex
  • SQL Server has to scan the index if there is Where on Second column of the Create Index
  • If there is any manipulation of the first column of the Create Index than it might have to do a scan of the whole Index
    • This depends upon how you are writing your query
  • Always check Predicate for a seek to find any residual predicate inside
  • Include columns are just included wihtout any sort
  • To avoid Key Lookups the column being looked up can be added in Include

Leave a Reply

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

You are commenting using your 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