I have a large partitioned table by month (70 Billion rows)
It has 2 indexes
- Clustered index on deviceId, logDateTime, eventId, id (unique)
- Non Clustered index on id (unique) - Primary Key
the first clustered index is aligned with the partitions, the 2nd non-clustered Primary key is non-aligned on the primary filegroup
I want to align the 2nd nonclustered index with the partition to make it easy to truncate parttion data etc.
from what i've read I need to drop the index and recreate it, can i create it first then delete the old one and set the new one as a primary key?
I'm looking for a solution with the least/ no downtime since its a 24/7 database
Sql server 2017 enterprise
the current bad index is
ALTER TABLE [dbo].[LogData] ADD CONSTRAINT [PK_LogData] PRIMARY KEY NONCLUSTERED ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, ONLINE = ON, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 99) ON [PRIMARY] GO
When i look at creating a new index, I either have a file group or partition scheme if I choose the partition scheme there is only one called PartitionByMonthScheme and it only allows me to choose datetime as the column, this generates the follow
CREATE UNIQUE NONCLUSTERED INDEX [ix_newindex_id] ON [dbo].[LogData] ( [id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PartitionByMonthScheme]([LogDateTime])
I'm assuming this is correct, and after I do tis it will align the new index with the partition? and then i have to delete the old PK index and make this new index the primary key?
or should i be unlinking the primary key first and then doing the index creation with primary key as one operation?
Advice and help on achieving the above will be appreciated. Thanks