SQL SERVER – Order Of Column In Index

I just found one of my Jr. DBA to create many indexes with lots of column in it. After talking with him I found out that he really does not understand how really Index works. He was under impression that if he has more columns in one index, that index has higher chance of getting selected during execution of query and speed up the query. It was very much incorrect. He did not understand important of the order of column in created index.

Order really matters and the column which is at first order matters the most in Index. The selection of index is entirely based on first column. An index is considered for use only if the first column listed in the index is used in the query. So if there is no match on first column and column used in JOIN, ORDER BY, or WHERE clauses of the query, index is completely ignored. It also does not speed up index performance if many columns are used while creating index. It may slow down the usage of the table instead.

Ideally, create small index on most important columns used in JOIN, ORDER BY, or WHERE clauses of the query.

Reference : Pinal Dave (https://blog.sqlauthority.com)

, ,
Previous Post
SQL SERVER – SQL SERVER – UDF – Get the Day of the Week Function – Part 4
Next Post
SQL SERVER – Example of DISTINCT in Aggregate Functions

Related Posts

4 Comments. Leave new

  • can u gve one example & explain as im new to indexes

    thanks

    Reply
  • Thanks.. I was wondering because in execution plan it was not considering index. Now I understand that the column needed was not first column in index. I left trying at that time. But now i’ll correct it.

    By the way… this site has helped me a lot improving my knowledge. Thank you for all.

    Reply
  • Tanx so nice

    Reply
  • Tugay Çalık
    June 27, 2013 2:00 am

    SELECT UnitPrice, ModifiedDate
    FROM Sales.SalesOrderDetail
    WHERE SalesOrderDetailID BETWEEN 1 AND 100

    Can you explain the index behaviour of the below query. SalesOrderDetailID is not the first key column of any non clustered index but query optimizer choses it i know the primary key includes all nonclustered index but index order matters on searching. Doesn’t this execution plan violate it

    Reply

Leave a Reply

Menu