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
being a dba 