These are my observations about your answer, they're not wrong but I can partly understand the interviewer.
first, check whether the query is using index? If yes, check it is a index scan or index seek.
How would you do that? You're missing the previous steps to get here.
Is index really necessary?
second, check how the developer write the sql query:
1. Don't select any unneccessary columns, because it can cause key lookup
How do you define which columns are not necessary?
2. check columns on join clause that whether they have indexes, if not, suggest admin to create
That's a good option but you were told you can't change them, so you need to work on what you have.
3. Check where clause, if using <> or like '%...%',... the index will not be used.
You can change this, but only if it's not really needed or you can change it to something SARGable.
4. avoid using sort, we can remove it and sort on application, for example, sort on C# code when displaying the result for client. The interviewer didn't agree with me about the idea
Sort is expensive but you'll end up doing it anyway if needed. It's like delivering the problem to someone else, but the problem still exists.
5.don't use function on where clause
Don't use system functions on where clause and don't use UDF anywhere. UDF degrade performance anywhere they're used (except for iTVF).
6. and many thinks developer should know, they can search on google
I didn't get this. However, google won't help you if you don't know what to look for.
and final, check server configuration:
- Check auto_close, auto_shrink (this can raise index fragmentation), auto_updatestats...check I/O setup
Same thing as before, you should focus on the query.
Again, there's no perfect answer and you're not wrong, but you could have explained more things to demonstrate you know what you're doing and you're not repeating something you learned on a SQL course.
Luis C.General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?How to post data/code on a forum to get the best help: Option 1 / Option 2