which operation will be performed by SQL Server?

Chaitanya Kiran 696 Reputation points
2022-02-03T03:48:05.307+00:00

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?

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,675 questions
0 comments No comments
{count} votes

4 answers

Sort by: Most helpful
  1. Olaf Helper 40,736 Reputation points
    2022-02-03T04:41:24.76+00:00

    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


  2. Ronen Ariely 15,096 Reputation points
    2022-02-03T06:31:18.147+00:00

    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

    170790-image.png

    0 comments No comments

  3. YufeiShao-msft 7,056 Reputation points
    2022-02-03T07:14:57.447+00:00

    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.


  4. Tom Phillips 17,716 Reputation points
    2022-02-03T16:23:18.187+00:00

    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.

    0 comments No comments