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

• Andy DB Analyst: You need to add a column LogDateTime in your new unique index to be able to align it. So you don't have to drop the old one. After creating index you just need to change a PK

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.

• Quade: thanks, we do require the id since the (IOT) devices table has a latestRecordId column for fast lookups of latest data, but i dont think it has to be primary key ?
• Dan Guzman: @Quade, is the latestRecordId the same as the id column in your question? Is it an IDENTITY column? The composite unique index I suggested won't enforce uniqueness on id alone but it will help optimize singleton lookups.
• Quade: yes, devices.latestRecordId points to the LogData.id table for singleton lookups, and the LogData.id PK is a bigint with IDENTITY seed that auto accumulates
• Dan Guzman: @Quade, as long as no foreign keys reference LogData, you should be able to align the table/indexes as I mentioned in my answer.
• Quade: thanks @Dan, so the table does not need a primary key then? and i can create a unique non clustered index with id,logDateTime, and when i do sington lookups i must use id and logdatetime? or will using the id alone on lookups still perform ok?