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 (http://blog.SQLAuthority.com)

5 thoughts on “SQL SERVER – Order Of Column In Index

  1. 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.

    Like

  2. Pingback: SQL SERVER – Weekly Series – Memory Lane – #032 | Journey to SQL Authority with Pinal Dave

  3. 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

    Like

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s