How to create aligned index (PK) on partitioned table and delete the non-aligned index?

Question

I have a large partitioned table by month (70 Billion rows)

It has 2 indexes

1. Clustered index on deviceId, logDateTime, eventId, id (unique)
2. 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

Answer 1

Aligned means the table (clustered index here) and all non-clustered indexes must be partitioned using the same partition scheme or partition schemes based on the same partition function. This ensures partition boundaries are identical to permit partition-level operations like TRUNCATE or SWITCH.

The issue you have is that the partitioning column must be a key column of partitioned primary keys (as well as partitioned unique indexes and unique constraints). Consequently, you will need to add logDateTime to the primary key in order to align the table/indexes.

Before you go there, I suggest you first verify the primary key is actually needed/used. Log tables are often not part of a relational model and thus do not require a primary key (although common in the wild in my experience). If that is your situation, simply drop the PK to align the table.

Assuming the application does not require a primary key on id alone, you can create a new unique index (or unique constraint) on id and logDateTime and drop the old primary key afterwards like below.

CREATE UNIQUE NONCLUSTERED INDEX [idx_LogData] ON dbo.LogData
    (
        id ASC
       ,logDateTime
    ) 
    WITH (ONLINE = ON)
    ON PartitionByMonthScheme(logDateTime);
GO
ALTER TABLE dbo.LogData DROP CONSTRAINT PK_LogData;
GO

Alignment is not an option if you must have a primary key on id alone.