At the interview, you may be asked a question about database indexes. And here is a very simple question that even an experienced developer can stumble on.
Question
The table has an index(IX_Salary) on the Salary column. Will it work for this SQL Statement?
SELECT Count(1)
FROM [YarkulTestDb1].[dbo].[Person]
WHERE Salary + 50 > 1000
Answer
No, because index does not work in case there is an operation on column in WHERE expression. It will not be able to use that index to optimize the query because it is not filtering on the salary column directly. Instead, it is performing a calculation on the salary column (Salary + 50) and then filtering on the result of that calculation (> 1000). In this case, the query optimizer will likely have to scan the entire table to find the rows that match the condition.
Case IX_Salary works well
Case IX_Salary does not do its job
About Me
I am a Full Stack Developer with +15 years of experience. Learning new concepts and always looking for new challenges. Visit my personal blog https://yarkul.com/ to learn .NET, SQL, and Web Development.
Top comments (0)