How can I create a partitioned table with the addition of a unique index in SQL Server?

Question

I am creating a partitioned table called TestArticles, specifying several filegroups according to the year of their publication (publishDate). This code (excluding the commented parts) executes correctly. My task is to add a unique index to the 'hash' field. When I try to do this in the table creation code, I receive the following error:

'Column 'publishDate' is a partitioning column of the index 'UQ_Articles_hash. Partitioning columns of a unique index must be a subset of the index key.'

I can create a composite primary key from (id, publishDate, hash) - but that is not what is required of me.

Is there any way to specify hash as a unique index for each created filegroup or to designate it as such when initializing the entire table?

USE Articles;
GO
ALTER DATABASE Articles
ADD FILEGROUP Articles2024;
ALTER DATABASE Articles
ADD FILEGROUP Articles2025;
ALTER DATABASE Articles
ADD FILEGROUP Articles2026;
ALTER DATABASE Articles
ADD FILEGROUP Articles2027;
CREATE PARTITION FUNCTION PF_Articles_PublishDate (DATETIME)
AS RANGE RIGHT FOR VALUES 
(
'2024-01-01', 
'2025-01-01',
'2026-01-01'
);
CREATE PARTITION SCHEME PS_Articles_PublishDate
AS PARTITION PF_Articles_PublishDate
TO 
(
Articles2024,
Articles2025,
Articles2026,
Articles2027
);
CREATE TABLE TestArticles (
    id INT NOT NULL,
    path VARCHAR(200) NULL,
    description VARCHAR(100) NOT NULL,
    publishDate DATETIME NOT NULL,
    hash BIGINT NOT NULL,
    authorId INT NOT NULL,
    CONSTRAINT PK_Articles PRIMARY KEY CLUSTERED (id, publishDate),
    CONSTRAINT FK_Articles_Authors FOREIGN KEY (authorId) REFERENCES dbo.Authors(id)
    --,CONSTRAINT UQ_Articles_hash UNIQUE NONCLUSTERED (hash)
) ON PS_Articles_PublishDate (publishDate);

Database: MS SQL Server 2022 Version: 16.0.1000.6 Error details: Msg 1908; Level 16; State 1.

Erik Reasonable Rates Darling · Accepted Answer · 2024-11-15T14:44:34.327

run away

You can't do that with Partitioning, at least not in a way that you'd probably like. I've omitted the foreign key from this table as I don't have the corresponding table definition.

DROP TABLE IF EXISTS
    dbo.TestArticles;
CREATE TABLE 
    dbo.TestArticles
(
    id integer NOT NULL,
    path varchar(200) NULL,
    description varchar(100) NOT NULL,
    publishDate datetime NOT NULL,
    hash bigint NOT NULL,
    authorId integer NOT NULL,
    pf AS $PARTITION.PF_Articles_PublishDate(publishDate) PERSISTED /* NEW! /
    CONSTRAINT 
        PK_Articles
    PRIMARY KEY CLUSTERED 
        (id, publishDate) 
    ON PS_Articles_PublishDate
        (publishDate),
    CONSTRAINT 
        UQ_Articles_hash 
    UNIQUE NONCLUSTERED 
        (hash, pf, publishDate) / NEW! */
    ON PS_Articles_PublishDate 
        (publishDate)
) ON PS_Articles_PublishDate(publishDate);

You do need to include PublishDate in any unique indexes:

SQL needs the partitioning key to be explicitly defined in all unique indexes on partitioned tables. This is so that SQL can determine the uniqueness of that index by checking one partition.

You would end up living in Interesting Times™️ and dealing with all sorts of issues unique to this arrangement, beyond the muck and mire that one must contend with when using less obtuse Partitioning schemes.

If you do go this route, you may want to consider enabling Trace Flag 176 as a startup parameter.

I would not generally recommend following this approach unless you feel like you're not working enough hours and you have too much free time, or you're enjoying life outside of work a little too much.

If you'd like a somewhat easier approach, you may want to use Partitioned Views instead. Since partitioned views are multiple separate tables, indexes are per-table, and there's no requirement for the "partitioning key" to be part of them.

I wrote a couple posts about them during my time at Brent Ozar Unlimited, which you can find here:

As a side note:

Database: MS SQL Server 2022 Version: 16.0.1000.6

For the love of god install CU16 and get off RTM.

Martin Smith · Answer 2 · 2024-11-14T18:29:25.017

Is there any way to specify hash as a unique index for each created filegroup?

From your example code your partition scheme has a partition per year and you have a filegroup per partition so I am going to treat this as largely interchangeable with

Is there any way to specify hash as a unique index for each year?

If you create an int partition scheme instead. e.g. with initial values 2024,2025,2026 you can then do

CREATE TABLE TestArticles (
    id INT NOT NULL,
    path VARCHAR(200) NULL,
    description VARCHAR(100) NOT NULL,
    publishDate DATETIME NOT NULL,
    hash BIGINT NOT NULL,
    authorId INT NOT NULL,
    CONSTRAINT PK_Articles PRIMARY KEY CLUSTERED (id, publishDate, PartitionYear),
    PartitionYear AS YEAR(publishDate) persisted,
    CONSTRAINT UQ_Articles_hash UNIQUE NONCLUSTERED (hash, PartitionYear)
) ON PS_Articles_PublishYear (PartitionYear);

This will enforce that the hash is unique for a whole year not just unique for a specific publishDate datetime value.

As long as you are on top of partition maintenance and keep the 1 year = 1 partition = 1 file group pattern this will also ensure uniqueness within each partition /file group too.

It does mean that your PK ends up including an annoying extra column but this doesn't affect the uniqueness guarantees of it as it is functionally dependent on publishDate.

And I guess the only reason it was ever bundling publishDate along with id could well have been for partition alignment so in that case you can just replace with (id, PartitionYear)

How can I create a partitioned table with the addition of a unique index in SQL Server?

2 Answers2

run away

Linked