Can I merge these two Sql Server index into one:

34gzjxbg  于 2023-06-21  发布在  SQL Server
关注(0)|答案(1)|浏览(123)

I have a table in Sql server 2022 with more tahn 100 milion records, Can I merge these two index into one:

create nonclustered index [IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendStatus] on [dbo].[SMSInOutBoxDetails]
    (
     [SMSInOutBox_ID] asc,
     [SendStatus] asc
        )
    include ([Cost]) on [PRIMARY]
create nonclustered index [IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendTime] on [dbo].[SMSInOutBoxDetails]
    (
     [SMSInOutBox_ID] asc,
     [SendTime] asc
        )
    include ([ID], [Number], [Delivery], [Cost], [RefId], [SendStatus], [IsRead], [MobileOperator],
             [WebServiceOutboxDetailId], [Order],
             [sort]) on [PRIMARY]

Merge Index:

create nonclustered index [IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendStatus_SendTime] on [dbo].[SMSInOutBoxDetails]
    (
     [SMSInOutBox_ID] asc,
     [SendStatus] asc,
     [SendTime] asc
        )
    include ([ID], [Number], [Delivery], [Cost], [RefId], [IsRead], [MobileOperator], [WebServiceOutboxDetailId],
             [Order], [sort])
    on [PRIMARY]
e4eetjau

e4eetjau1#

This is not an automatic quick win merge candidate. When you try to combine them you need to select whether to go for

[SMSInOutBox_ID], [SendStatus], [SendTime] or for [SMSInOutBox_ID], [SendTime], [SendStatus] .

Your proposed index will support a superset of the queries that IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendStatus does (though maybe at the expense of some logical reads as it won't be as compact as the existing one).

But it won't support all the queries that IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendTime does.

For example if you have a query

SELECT SMSInOutBox_ID,
       SendStatus,
       Cost,
       SendTime,
       ID,
       Number,
       Delivery,
       RefId,
       IsRead,
       MobileOperator,
       WebServiceOutboxDetailId,
       [Order],
       sort
FROM   dbo.SMSInOutBoxDetails
WHERE  [SMSInOutBox_ID] = @SMSInOutBox_ID
       AND [SendTime] > @SendTime
ORDER  BY [SendTime]

Then IX_SMSInOutBoxDetails_SMSInOutBox_ID_SendTime will support returning this from a single index seek without any sort operations.

If you only have a limited number of send statuses though (maybe Delivered, Sent, Rejected and Failed?) your new index could still support this but it is more cumbersome (and also runs a risk that new statuses might be added without being reflected here)

WITH T AS
    (
    SELECT SMSInOutBox_ID,
           SendStatus,
           Cost,
           SendTime,
           ID,
           Number,
           Delivery,
           RefId,
           IsRead,
           MobileOperator,
           WebServiceOutboxDetailId,
           [Order],
           sort
    FROM   dbo.SMSInOutBoxDetails
    WHERE  [SMSInOutBox_ID] = @SMSInOutBox_ID
               AND [SendTime] > @SendTime
   )
   SELECT *
   FROM T
   WHERE SendStatus = 1
   UNION ALL
   SELECT *
   FROM T
   WHERE SendStatus = 2
   UNION ALL
   SELECT *
   FROM T
   WHERE SendStatus = 3
   UNION ALL
   SELECT *
   FROM T
   WHERE SendStatus = 4
   ORDER BY SendTime, ID /*I have assumed ID is a clustered primary key here*/

The above does 4 independent index seeks that will bring back the rows in order of SendTime, RowLocator and combines them with a merge union to bring back the global resultset without any sorting ( Fiddle ).

相关问题