How to convert index from scan to seek?

Question

I have a sql query which contains multiple table joins. Already created non clustered indexes. I have optimized the query as much as possible based on looking at SQL quey execution plan. I found some of the indexes being scanned. My question is how exactly i convert index scan to index seek?? What exact parameters needs to be consider for this?

Amira Bedhiafi · Answer

It depends on your query and how you are trying to access your data from that indexed field. You need to know more about SARGability : SARGAbility indicates whether or not a filter expression (predicate) can use an index search. If so, the predicate is said to be sargable, the index is used for a search and the response time will be excellent. Otherwise, the response time will be bad because it will be necessary to sweep all the rows of the table to satisfy the request ... Are SARGable: searches that relate to the same sorting direction as that performed for the structuring of the index (alphabetical for character strings, numeric for numbers, temporal for dates, etc.). Are not SARGAble: The NOT is in principle never sargable, just like the OR and the IN with several values. Likewise, a column to which an operation is applied, just like a column "embedded" in a function, is in principle never sargable. On this subject, beware of implicit casts! You can share your query plan here https://www.brentozar.com/pastetheplan/ so we can help you

Jignesh Kumar · Answer

Hi, Please refer this link, I think you can create composite cluster Index which will help you towards your query, https://www.red-gate.com/simple-talk/sql/performance/identifying-and-solving-index-scan-problems/

Naresh Beniwal · Answer

https://stackoverflow.com/questions/42785294/how-to-change-index-scan-to-index-seek-in-this-case