0

If I have two indexes:

IDX_1 = (COL1), (COL2) DESC
IDX_2 = (COL1) INCLUDE (COL3)

will a single defined below cover both queries? does the Descending keyword have any bearing on the usage of INCLUDE statement for covering both queries?

IDX_3 = (COL1) INCLUDE (COL2), (COL3)
Community
  • 1
DamagedGoods
  • 2,591
  • 6
  • 34
  • 48

2 Answers2

2

You didn't show us any queries.

Perhaps you asked whether IX3 will be a full substitute for 1 and 2? If so:

Pretty much. Except:

  1. For IX1, SQL Server can navigate the index tree to find all values for COL1 and keep navigating the tree to find all COL2.
  2. Also, IX1 can potentially assist in eliminating a sort for COL1, COL2 DESC.

Except for above cases, as far as I can see, IX3 will be a substitute for both IX1 and IX2 (pls comment other cases I missed and I'll add them to the above list).

DESC has no bearing on included columns. They are just "brought along", they don't influence any sorting for the index (and the values are only in the leaf).

Tibor Karaszi
  • 18,269
  • 2
  • 17
  • 30
0

No. this will not cover the two indexes. Lets review those: 

IDX_1 = (COL1), (COL2) DESC

This one is indexing by COL1 as primary and COL2 as secondary

IDX_2 = (COL1) INCLUDE (COL3)

This one is indexing by COL1 one exclusively. the INCLUDE is just a data column added to the index, not indexed by.

IDX_3 = (COL1) INCLUDE (COL2), (COL3)

This index is ordered by COL1, just like IDX_2, include in its data, the information from the other two columns.

Depends on your queries you could have try index like this: 

IDX_4 = (COL1), (COL2) INCLUDE (COL3)

This is ordered by COL1 First, Secondary is COL2, Including Col3 data in the index but not relevant to the indexing order.

Nathan L
  • 11
  • 2