Multiple non clustered indexes OR compound non clustered index?

• MuditGupta

  SSCrazy

  Points: 2596

  More actions

  October 9, 2008 at 7:35 pm

  #199537

  Hi there,

  I have some doubts over design of Non Clustered Indexes and I would really appreciate if I some one can shed some light on design issues regarding the same.

  To begin with lets say there is a table1

  Table Name: TestTable

  column Name: A, B, C, D, E, F, G

  Now, There is a Clustered Index on Columns A, B & C

  For simplicity, lets assume that Columns A,B & C are Char and rest have datatype as Datetime

  Next, there are some queries where WHERE clause involves following:

  1) D AND E

  2) E AND F

  3) E AND G

  4) F AND G

  5) D AND E AND G

  OR I should say some combination of columns which are not part of Clustered Index.

  Here, in deciding upon Non Clustered Index, Is it So that

  Statistics are only kept on the first column in any non clustered index i.e. SQL Server will use the first field to determine whether an index should be used.

  Having Non Clustered indexes each on D, E and F would be better choice than having multiple Non clustered index on (D,E,G), (E,F) etc...

  Thanks.

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  October 10, 2008 at 2:41 am

  #883310

  Statistics are only kept on the first column in any non clustered index i.e. SQL Server will use the first field to determine whether an index should be used.

  Yes and no. If you have a composite index and a query that filters on all the columns included in the index, the SQL can do a straight index seek, match all the columns and be done. If the index only contains some of the columns, then SQL will seek on the columns that are in the index, do a bookmark lookup to the cluster to fetch the remaining columns, then do a filter to remove unwanted rows.

  Needless to say, the first way is faster than the second.

  To have all of the queries you listed running as optimally as possible, you would need 4 NC indexes

  D, E, G

  E, F

  E, G

  F, G

  It may be that you decide that some queries aren't as important as others and so you may leave some of those off to save space and reduce impact on inserts/updates

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass