Início / dba / Perguntas / 164179
Accepted
Alexei
Asked: 2017-02-14 06:50:42 +0800 CST

Como lidar com a exclusividade de muitas colunas grandes no SQL Server?

  • 772

Tenho a seguinte tabela:

CREATE TABLE dbo.Document
(
    DocumentId int IDENTITY(1,1) NOT NULL CONSTRAINT PK_DocumentPRIMARY KEY CLUSTERED,
    [Timestamp] datetime2(7) NOT NULL CONSTRAINT DF_Document_Timestamp  DEFAULT (getdate()),
    CreatedBy nvarchar(128) NOT NULL CONSTRAINT DF_Document_CreatedBy  DEFAULT (dbo.getCurrentUser()),
    MonthId int NOT NULL,
    TimeModeId int NOT NULL CONSTRAINT FK_Document_TimeMode REFERENCES usr.TimeMode,
    Key1 bit NOT NULL,
    Key2 int NULL,
    Key3 varchar(max) NULL,   -- sometimes above 8000chars
    Key4 varchar(max) NULL,   -- sometimes above 8000chars
    Key5 varchar(max) NULL,   -- sometimes above 8000chars
    Key6 varchar(max) NULL,   -- sometimes above 8000chars
    Key7 varchar(max) NULL,   -- sometimes above 8000chars
    Key8 int NOT NULL,
    CONSTRAINT FK_Document_BrandType FOREIGN KEY(Key8) REFERENCES dbo.BrandType (Key8),
)

Embora eu tenha insistido em encontrar um identificador natural melhor, tive que lidar com a seguinte tupla única natural:

MonthId, TimeModeId, Key1, ... , Key8

Isso é muito grande para um índice UNIQUE (máximo de 900 bytes no SQL Server 2014 ou menos), então eu tive que criar algo. Minha ideia é calcular um hash para essas colunas, então eu tinha PERSISTED COMPUTEDcolunas como acima:

FiltersHash  AS (hashbytes('SHA2_256',(
        (((((((((((((((
            (CONVERT(varchar(10),MonthId)+'|') 
            + CONVERT(varchar(4),TimeModeId))
            +'|')+CONVERT(varchar(4),Key1))
            +'|')+isnull(CONVERT(varchar(max),Key2),''))
            +'|')+isnull(CONVERT(varchar(max),Key3),''))
            +'|')+isnull(CONVERT(varchar(max),Key4),''))
            +'|')+isnull(CONVERT(varchar(max),Key5),''))
            +'|')+isnull(CONVERT(varchar(max),Key6),''))
            +'|')+isnull(CONVERT(varchar(max),Key7),''))
            +'|')+isnull(CONVERT(varchar(4),Key8),''))
        ) PERSISTED,
CONSTRAINT UQ_Document_FiltersHash UNIQUE NONCLUSTERED (FiltersHash),

Ele se mostrou útil porque, por meio de um cenário complicado, o aplicativo tentou duplicar um documento.

Pergunta: esta solução é boa ou existem soluções mais simples ou mais eficientes para o problema de unicidade de grande largura?

Nota: No meu aplicativo, posso ignorar com segurança as colisões (se isso acontecer, as consequências são bastante pequenas). Obrigado Aaron Bertrandpor apontar.

sql-server sql-server-2014

1 respostas

  1. Best Answer

    A chance de uma colisão de hash é bastante astronômica (como discutido em outro lugar no Stack Exchange: https://stackoverflow.com/a/4014407 ). No entanto, você pode reduzi-lo ainda mais adicionando uma segunda chave:

    ...
FiltersHash  AS HASHBYTES('SHA2_256', /* Various fields */) PERSISTED,
KeyPrefixes  AS CAST(Key1 AS CHAR(1) + '|' + CAST(Key2 AS VARCHAR(10))
    + '|' + LEFT(Key3, 100) + '|' + LEFT(Key4, 100)
    + '|' + LEFT(Key5, 100) + '|' + LEFT(Key6, 100)
    + '|' + LEFT(Key7, 100) + '|' + CAST(Key8 AS VARCHAR(10)) PERSISTED
...
CREATE UNIQUE INDEX UQ_Docs_BizKey ON Documents (FiltersHash, KeyPrefixes)

    Agora, dois registros devem corresponder em três campos e na primeira parte de mais cinco. Se seus dados geralmente incluem tubos verticais, considere um separador alternativo. INSERTs na tabela serão um pouco mais lentos, mas em seus volumes de dados provavelmente não é uma preocupação.

    Como um aparte, provavelmente é melhor agrupar MonthIDe deixar DocumentIDum PK não agrupado, supondo que as pessoas ocasionalmente executem pesquisas por intervalo de data ("todos os documentos de junho"), mas raramente pesquisem por um intervalo de IDs de documentos.

    • 2

relate perguntas