Início / dba / Perguntas / 343858
Accepted
Michael J Swart
Asked: 2024-11-26 23:45:27 +0800 CST

Como adicionar uma restrição de verificação confiável rapidamente

  • 772

Estou adicionando uma restrição de verificação a uma tabela grande porque quero prepará-la para transformá-la em uma tabela particionada usando a alternância de partições.

A verificação é uma verificação de desigualdade simples em uma coluna e há um índice nessa coluna. Mas quando adiciono a restrição, o SQL Server ainda executa uma varredura de toda a tabela. É possível adicionar a restrição rapidamente e mantê-la confiável? Eu esperava que fosse possível por causa do índice.

Aqui está uma reprodução que mostra essa digitalização:

USE tempdb;
GO

SELECT * 
INTO MY_MESSAGES
FROM sys.messages;

CREATE CLUSTERED INDEX IX_MY_MESSAGES ON dbo.MY_MESSAGES(message_id);

SET STATISTICS IO ON;
ALTER TABLE dbo.MY_MESSAGES
    ADD CONSTRAINT CK_mymessages CHECK (message_id < 50000);
SET STATISTICS IO OFF;

DROP TABLE IF EXISTS dbo.MY_MESSAGES;

Isso é mostrado na aba de mensagens:

Table 'MY_MESSAGES'. Scan count 1, logical reads 10955
sql-server

3 respostas

  1. Best Answer

    Infelizmente, a lógica de verificação de restrição é muito básica e não aproveitará um índice da maneira que você espera. Ela não buscará valores que não atendam à restrição. Poderia, mas não faz.

    Como Dan diz , um índice fino não agrupado pode tornar a varredura mais eficiente.

    Table 'MY_MESSAGES'. Scan count 1, logical reads 314, physical reads 0

    Você pode capturar o plano de consulta com o Query Store. Ele mostrará um scan, um Compute Scalar com uma CASEexpressão para testar a condição de restrição e um Assert para lançar um erro, se necessário.

    Plano de loja de consulta

    Com índice não agrupado

    [Expr1003] = Scalar Operator(
    CASE WHEN [dbo].[MY_MESSAGES].[message_id]>=(50000) THEN (0) ELSE NULL END)

    *** Output Tree: (trivial plan) ***

PhyOp_StreamCheck (Check COLUMN CNST: CK_mymessages COL: message_id) 
    PhyOp_ComputeScalar
        PhyOp_NOP
            PhyOp_Range 
                TBL: dbo.MY_MESSAGES(2) ASC  
                Bmk ( QCOL: [dbo].[MY_MESSAGES].message_id COL: Uniq1001 ) 
                IsRow: COL: IsBaseRow1000 
        AncOp_PrjList 
            AncOp_PrjEl COL: Expr1003 
                ScaOp_Intrinsic x_iopBuildSwitch
                    ScaOp_Comp x_cmpGe
                        ScaOp_Identifier QCOL: [dbo].[MY_MESSAGES].message_id
                        ScaOp_Const TI(int,ML=4) XVAR(int,Not Owned,Value=50000)
    ScaOp_Identifier COL: Expr1003
    • 3

  2. Uma varredura completa da tabela ou índice será necessária para garantir que os valores existentes estejam em conformidade com a expressão de restrição de verificação. Você pode ver uma ligeira melhoria de desempenho com um índice não clusterizado, message_idjá que menos leituras lógicas serão necessárias para a validação, mas cada linha no índice ainda precisará ser varrida.

    Estou adicionando uma restrição de verificação a uma tabela grande porque quero prepará-la para transformá-la em uma tabela particionada usando a alternância de partições.

    Considere particionar esta tabela de forma similar à tabela de partição de destino. Isso garantirá que os dados na partição de origem estejam em conformidade com os limites de partição da tabela de destino, permitindo o uso de SWITCHsem uma restrição de verificação confiável, evitando assim a necessidade de validar dados com uma varredura.

    • 2

  3. A restrição pode ser adicionada rapidamente usando "WITH NOCHECK" para ignorar dados existentes. Você pode confiar que a restrição será aplicada a dados novos/alterados. Você não saberá se os dados existentes estão em conformidade com a restrição de verificação. No entanto, você pode executar sua própria consulta de seleção para validar dados existentes. O PK pode ser usado para verificar usando lotes, sabendo que, uma vez que um intervalo seja verificado, ele permanecerá válido.

    ALTER TABLE TableName
WITH NOCHECK
ADD CONSTRAINT ConstraintName CHECK (Condition);
    • 0

relate perguntas