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)
4 Comments. Leave new
can u gve one example & explain as im new to indexes
thanks
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.
Tanx so nice
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