Looking for some advice before I follow my gut on this and rebuild the nonclustered index to lead with our partition key. This is an insert-only table which is never updated or deleted, we keep a sliding window my truncating/merging periods on the left and adding new periods on the right. The nonclustered index is constantly at 99% fragmentation due to the makeup of data we are inserted (many unique accountids and inventoryids). I'm wondering what the best way would be to keep a nonclustered index for looking by accountId without all the fragmentation or maybe I shouldn't worry about the fragmentation?
I'm aware that our table doesn't have an explicit unique index, we rely on the [UNIQUIFIER] that is added to changedAt automatically.
Usage pattern: Table is always queried with a date filter (ChangedAt)
CREATE PARTITION FUNCTION pf_Weekly_QuantityHistory (datetime2(2)) AS RANGE RIGHT FOR VALUES ( '01 Jul 2019','08 Jul 2019','15 Jul 2019',etc ) CREATE PARTITION SCHEME [ps_Weekly_QuantityHistory] AS PARTITION [pf_Weekly_QuantityHistory] ALL TO ( [ExampleFG] ); CREATE TABLE [dbo].[QuantityHistory]( [AccountId] [int] NOT NULL, [InventoryID] [int] NOT NULL, [QuantityBefore] [int] NULL, [QuantityAfter] [int] NULL, [ChangedAt] [datetime2](2) NOT NULL DEFAULT (getutcdate()) ) ON [ps_Weekly_QuantityHistory](ChangedAt) CREATE CLUSTERED INDEX [CX_QuantityHistory] ON [dbo].[QuantityHistory] ( [ChangedAt] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE) CREATE NONCLUSTERED INDEX [IX_AccountId] ON [dbo].[QuantityHistory] ( [AccountId] ASC, [InventoryId] ASC, [ChangedAt] ASC ) INCLUDE(QuantityBefore,QuantityAfter) WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, DATA_COMPRESSION=PAGE)