Início / dba / Perguntas / 343599
Accepted
Mark Kaz
Asked: 2024-11-14 20:46:23 +0800 CST

Como posso criar uma tabela particionada com a adição de um índice exclusivo no SQL Server?

  • 772

Estou criando uma tabela particionada chamada TestArticles, especificando vários grupos de arquivos de acordo com o ano de sua publicação (publishDate). Este código (excluindo as partes comentadas) é executado corretamente. Minha tarefa é adicionar um índice exclusivo ao campo 'hash'. Quando tento fazer isso no código de criação da tabela, recebo o seguinte erro:

'A coluna 'publishDate' é uma coluna de particionamento do índice 'UQ_Articles_hash. Colunas de particionamento de um índice exclusivo devem ser um subconjunto da chave do índice.'

Posso criar uma chave primária composta de (id, publishDate, hash) - mas não é isso que é exigido de mim.

Existe alguma maneira de especificar o hash como um índice exclusivo para cada grupo de arquivos criado ou designá-lo como tal ao inicializar a tabela inteira?

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

Banco de dados: MS SQL Server 2022 Versão: 16.0.1000.6 Detalhes do erro: Msg 1908; Nível 16; Estado 1.

sql-server

2 respostas

  1. Best Answer

    fugir

    Você não pode fazer isso com Partitioning, pelo menos não de uma forma que você provavelmente gostaria. Eu omiti a chave estrangeira desta tabela, pois não tenho a definição de tabela correspondente.

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

    Você precisa incluir PublishDateem quaisquer índices exclusivos :

    O SQL precisa que a chave de particionamento seja explicitamente definida em todos os índices exclusivos em tabelas particionadas. Isso é para que o SQL possa determinar a exclusividade desse índice verificando uma partição.

    Você acabaria vivendo em Tempos Interessantes™️ e lidando com todos os tipos de problemas exclusivos desse arranjo, além da lama e do lodo com os quais é preciso lidar ao usar esquemas de particionamento menos obtusos.

    Se você seguir esse caminho, talvez seja interessante considerar habilitar o Trace Flag 176 como um parâmetro de inicialização.

    Eu geralmente não recomendaria seguir essa abordagem, a menos que você sinta que não está trabalhando horas suficientes e tem muito tempo livre, ou que está aproveitando demais a vida fora do trabalho.

    Se você quiser uma abordagem um pouco mais fácil, pode usar Partitioned Views. Como as partições de views são várias tabelas separadas, os índices são por tabela, e não há necessidade de que a "chave de particionamento" faça parte deles.

    Escrevi alguns posts sobre eles durante meu tempo na Brent Ozar Unlimited, que você pode encontrar aqui:

    Como observação lateral:

    Banco de dados: MS SQL Server 2022 Versão: 16.0.1000.6

    Pelo amor de Deus, instale o CU16 e saia do RTM.

    • 4

  2. Existe alguma maneira de especificar o hash como um índice exclusivo para cada grupo de arquivos criado?

    No seu código de exemplo, seu esquema de partição tem uma partição por ano e você tem um grupo de arquivos por partição, então vou tratar isso como amplamente intercambiável com

    Existe alguma maneira de especificar o hash como um índice exclusivo para cada ano?

    Se você criar um intesquema de partição em vez disso, por exemplo, com valores iniciais, 2024,2025,2026você pode fazer

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

    Isso garantirá que o hash seja único para um ano inteiro, não apenas para um publishDatevalor de data e hora específico.

    Contanto que você esteja em dia com a manutenção de partições e mantenha o padrão 1 ano = 1 partição = 1 grupo de arquivos, isso também garantirá exclusividade dentro de cada partição/grupo de arquivos.

    Isso significa que sua PK acaba incluindo uma coluna extra irritante, mas isso não afeta as garantias de exclusividade dela, pois ela depende funcionalmente de publishDate.

    E eu acho que a única razão pela qual ele estava sendo agrupado publishDatepoderia idmuito bem ter sido para alinhamento de partição, então nesse caso você pode simplesmente substituir por(id, PartitionYear)

    • 4

relate perguntas