0

I have a Index_01 which is a subset of INDEX_02

CREATE NONCLUSTERED INDEX [Index01] ON [Tab1](
colA,colB,colC)
INCLUDE ( colD,colE,colF) 

CREATE NONCLUSTERED INDEX [Index02] ON [Tab1](
    colE,
    colF,
    colC,
    colD,
    colA,
    colC)
INCLUDE (colB,colZ)

Since the Index01 is the subset of Index02 can we delete the Index01? Are Index_01 and Index_02 same?

Thanks in advance.

Jaydip Jadhav
  • 12,179
  • 6
  • 24
  • 40
Anu
  • 11
  • 1
  • 2
  • 1
    Is a phone book sorted by first name, last name and residence a subset of one sorted by occupation, date of birth, measurements, residence, last name and first name? The data is there, but the use is radically different. – Jeroen Mostert Sep 29 '16 at 14:28
  • Both indexes work differently – Sandip - Frontend Developer Sep 29 '16 at 14:29
  • 1
    They are not subset, and work differently, I would suggest you read this question, it may help you. http://stackoverflow.com/questions/2292662/how-important-is-the-order-of-columns-in-indexes – EricZ Sep 29 '16 at 14:30
  • link was helpful. Thank You – Anu Sep 29 '16 at 14:42

1 Answers1

0

SQL Server builds a B-Tree Plus based on the columns that define the index. Index01 is built based on columns colA, colB and colC. The B-Tree build for Index02 is built based on colE, colF, colC, colD, colA, colCT.hey are very different indexes.

Even though Index01 includes colD,colE,colF that means that when a match is found based on colA, colB, colC if the query just needs the data that is found in columns colD,colE,colF SQL Server does not have to go further and go into the data - it has the information in the index that it can then return.

benjamin moskovits
  • 5,261
  • 1
  • 12
  • 22