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]
1条答案
按热度按时间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
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)
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 ).