主页 / dba / 问题 / 343599
Accepted
Mark Kaz
Asked: 2024-11-14 20:46:23 +0800 CST

如何在 SQL Server 中创建带有唯一索引的分区表？

  • 772

我正在创建一个名为 TestArticles 的分区表，根据发布年份 (publishDate) 指定多个文件组。此代码（不包括注释部分）执行正确。我的任务是向“哈希”字段添加唯一索引。当我尝试在表创建代码中执行此操作时，我收到以下错误：

“列‘publishDate’是索引‘UQ_Articles_hash’的分区列。唯一索引的分区列必须是索引键的子集。”

我可以从（id、publishDate、hash）创建一个复合主键 - 但这不是我的要求。

有没有办法将哈希指定为每个创建的文件组的唯一索引，或者在初始化整个表时将其指定为唯一索引？

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);

数据库：MS SQL Server 2022 版本：16.0.1000.6 错误详情：消息 1908；级别 16；状态 1。

sql-server

2 个回答

  1. Best Answer

    逃跑

    您无法通过分区来实现这一点，至少不是以您可能喜欢的方式。我从此表中省略了外键，因为我没有相应的表定义。

    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);

    您确实需要PublishDate在任何唯一索引中包含：

    SQL 需要在分区表的所有唯一索引中明确定义分区键。这样，SQL 就可以通过检查一个分区来确定该索引的唯一性。

    您最终会生活在有趣的时光™️中，并处理这种安排所特有的各种问题，而不仅仅是使用不太难处理的分区方案时必须应对的泥潭和泥沼。

    如果您确实选择这条路线，您可能需要考虑启用跟踪标志 176作为启动参数。

    我一般不建议采用这种方法，除非你觉得你的工作时间不够，有太多的空闲时间，或者你太享受工作以外的生活。

    如果您想要一种更简单的方法，您可能希望使用分区视图。由于分区视图是多个单独的表，因此索引是每个表的，并且不需要“分区键”成为其中的一部分。

    我在 Brent Ozar Unlimited 工作期间写过几篇关于它们的文章，你可以在这里找到：

    附注：

    数据库：MS SQL Server 2022 版本：16.0.1000.6

    为了上帝的利益，请安装CU16并退出 RTM。

    • 4

  2. 有没有办法将哈希指定为每个创建的文件组的唯一索引？

    从示例代码来看，您的分区方案每年有一个分区，每个分区有一个文件组，因此我将把它视为与

    有没有办法将哈希指定为每年的唯一索引？

    如果你创建一个int分区方案，例如使用初始值，那么2024,2025,2026你可以这样做

    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);

    这将强制哈希值在整整一年内都是唯一的，而不仅仅是在特定的publishDate日期时间值内是唯一的。

    只要您关注分区维护并保持 1 年 = 1 个分区 = 1 个文件组模式，这也将确保每个分区/文件组内的唯一性。

    这确实意味着你的 PK 最终会包含一个烦人的额外列，但这不会影响它的唯一性保证，因为它在功能上依赖于publishDate。

    publishDate我猜它被捆绑在一起的唯一原因id很可能是为了分区对齐，所以在这种情况下你可以用(id, PartitionYear)

    • 4

相关问题