Início / dba / Perguntas / 39431
Accepted
cubetwo1729
Asked: 2013-04-08 07:07:07 +0800 CST

Índice exclusivo para subconsulta com função RANK()

  • 772

Tenho as seguintes tabelas:

CREATE TABLE Revision (
    RevisionId INT PRIMARY KEY IDENTITY,
    UserName NVARCHAR(256) NOT NULL,
    DateTime DATETIME NOT NULL DEFAULT GETDATE()
)

CREATE TABLE MyEntity (
    MyEntityId INT NOT NULL,
    RevisionId INT NOT NULL FOREIGN KEY REFERENCES Revision (RevisionId),
    Deleted BIT NOT NULL DEFAULT 0,
    Name NVARCHAR(256) NOT NULL,
    Body NVARCHAR(MAX) NOT NULL,
    PRIMARY KEY (MyEntityId, RevisionId)
)

Ou seja, a tabela MyEntity contém todas as alterações em uma entidade. Quando uma entidade é criada, modificada ou excluída, um novo registro é inserido em MyEntity e em Revision, para que todo o histórico seja rastreado.

Gostaria de uma view para ter a última versão de cada entidade:

CREATE VIEW MyEntityLatest WITH SCHEMABINDING AS
SELECT
    Latest.MyEntityId,
    Latest.Name,
    Latest.Body
FROM dbo.MyEntity
INNER JOIN (
    SELECT
        MyEntityId,
        RevisionId,
        Name,
        Body,
        RANK() OVER (PARTITION BY MyEntityId ORDER BY RevisionId DESC) AS RevisionIdDesc
    FROM MyEntity
    WHERE Deleted = 0
) AS Latest
    ON dbo.MyEntity.MyEntityId = Latest.MyEntityId
    AND dbo.MyEntity.RevisionId = Latest.RevisionId
WHERE Latest.RevisionIdDesc = 1

No entanto, gostaria de criar um índice exclusivo (e, portanto, restrição) de modo que o Nome seja exclusivo apenas para a revisão mais recente. Não consigo criar um índice na exibição, devido à subconsulta na exibição.

Como posso fazer isso?

sql-server database-design

1 respostas

  1. Best Answer

    Confiar na ordem de RevisionIddeterminar a revisão mais recente é incrivelmente perigoso e aponta para um problema de design aqui.

    Como você está usando o SQL Server 2008+, o que eu recomendaria é adicionar um sinalizador de bit IsLatestà MyEntitytabela para identificar a revisão mais recente. Em seguida, crie um índice filtrado exclusivo para restringir cada entidade a ter apenas uma única revisão mais recente (tecnicamente, isso não é necessário devido ao que você pediu, mas é uma ideia muito boa):

    CREATE UNIQUE NONCLUSTERED INDEX UC_MyEntity_IsLatest_MyEntityId
    ON [dbo].[MyEntity](IsLatest, MyEntityId)
        WHERE (IsLatest = 1);

    Em seguida, use a mesma técnica para impor a restrição no nome:

    CREATE UNIQUE NONCLUSTERED INDEX UC_MyEntity_IsLatest_Name
    ON [dbo].[MyEntity](IsLatest, Name)
        WHERE (IsLatest = 1);

    Você precisará gerenciar o sinalizador desativando-o da revisão "anteriormente mais recente" antes de inserir a "nova revisão mais recente", o que não é muito trabalho extra. Vou deixar para você determinar como isso deve funcionar com a Deletedbandeira envolvida.

    Essa solução reforça a restrição por design e simplificará bastante a definição da exibição, que talvez você nem precise indexar separadamente. (Indexar a Bodycoluna que é nvarchar(MAX)um pouco suspeito, mas é um problema separado.)

    Observação: incluí a IsLatestcoluna na chave dos índices de propósito, pois o otimizador não é inteligente o suficiente para incluir implicitamente o valor dessa coluna do predicado do filtro (como o predicado é arbitrário, presumo que nem tente ). Isso é o que permitirá que o índice seja usado quando você especificar WHERE IsLatest = 1em uma consulta.

    • 1

relate perguntas