which operation will be performed by SQL Server?

Question

I have a table called customers having columns id, name, city, state.

I have a non clustered index on name and city columns. If I execute "select * from customers", which operation will be performed by SQL Server?
If I execute "select * from customers where id=10" which operation will be performed by SQL Server?
If I execute "select * from customers where city=New York", which operation will be performed by SQL Server?

Answer 1

which operation will be performed by SQL Server?

Sorry, but for me it's not clear what you mean with "operation"?
If you run a query, it's executed by the database engine.
For more details see Query Processing Architecture Guide

Answer 2

Good day @Chaitanya Kiran

which operation will be performed by SQL Server?

a component called the Query Optimizer check the query, the server configuration and database schema, and the database statistics (the actual data which you have in your table). According to the input it builds a query execution plan. As the Execution plan is depended on multiple parameters the same query which is executed on two tables with the same structure might use different Execution Plan or in other world us different operations.

You best option is simply check what is your the operation by checking the actual exception plan in your server.

In order to check which operation will be performed by SQL Server, you simply need to check the Execution Plan. This is done simply by clicking the button Include Actual Execution Plan in the toolbar and execute your query

Answer 3

Hi @Chaitanya Kiran ,

When you execute the SQL Query, click on the Display Estimated Execution Plan button or simply click (Ctrl + L) which will open the Display Estimated Execution Plan window
Non-Clustered Index in SQL Server

All of the fields in a select can be fullfiled from a non clustered index, but where the selectivity or indexing of the query is too poor to result in an Seek

-------------

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 4

You did not give us enough information to answer your question.

In any case, the answer is "it depends". There are many variables on the query plan, what columns did you return, how many rows exist in the table, how many rows exist matching your WHERE clause, etc. You must test it yourself and look at the generated query plan to determine what the query optimizer CHOSE to do at that particular time. That doesn't mean it will always choose to do it the same way next time.