CODEWITHSUNDEEP
sqlsql-server
tanu
tanu

Reputation: 65

In what case SQL will use an Index of the table

During an interview I was asked a weird question. I could not find the correct answer for it, so posting the question below:

I have an index on a column Stud_Name. I am searching for name using a wild card. My query is

a) select * from Stud_Details where Stud_Name like 'A%'

b) select * from Stud_Details where Stud_Name like '%A'.

c) select * from Stud_Details where Stud_Name not like 'A%'

In which case would the SQL server use the Index, that I have created on Stud_Name?

PS: If this question seems idiotic don't get mad on me, get mad on the interviewer who asked this to me. Also I don't have any info regarding how the index was created. This info above is all I have.

Upvotes: 1

Views: 300

Answers (2)

Atharva
Atharva

Reputation: 1

Indexes are optimized for searching from the beginning of the string. They work by efficiently locating the rows where the search value matches with the initial part of the indexed column. In option (a), the wildcard % is placed at the end of the search string ('A%'). This allows the index to be used because it can start searching at the beginning of the Stud_Name values in the index and identify matching names that start with 'A'.

Upvotes: 0

Martin Smith
Martin Smith

Reputation: 453287

In what cases can SQL Server use an Index on Stud_Name?

  • Option (a) is the only one that can be used in an index seek. like 'A%' can get converted to a range seek on >= A and <B

  • Option (b) can't use an index seek as the leading wildcard prevents this. It could still scan an index though.

  • Option (c) could in theory be converted to two range seeks (< 'A' OR >= 'B' but I've just checked and SQL Server does not do that (even in cases where this would eliminate 100% of the table and with a FORCESEEK hint). Again it can scan an index though.

In what cases will SQL Server use an Index on Stud_Name?

This depends on cardinality estimates and whether the index is covering or not and the relative width of the index rows vs the base table rows.

Assuming the index is not covering then any rows found that match the WHERE clause will need lookups to retrieve the column values. The greater the number of estimated lookups the less likely the non covering index is to be used.

For b+c the choice is index scan + lookups vs table scan with no lookups. The favourability of doing an index scan will be higher if the index is much narrower than the table. If they are similar sizes there is not much IO benefit from reading the index rather than the table in the first place.

Upvotes: 3

Related Questions