Início / dba / Perguntas / 235172
Accepted
Zikato
Asked: 2019-04-19 06:32:17 +0800 CST

Por que o SQL Optimizer não usa minha restrição?

  • 772

Eu quero criar uma tabela com uma NOT NULLcoluna bool.
Eu uso TINYINTcom CHECKrestrição BETWEEN 0 and 1. A restrição é nova e, portanto, confiável

Agora, eu esperaria que o otimizador de SQL agora saiba que esta coluna só pode ser 0 e 1, então, quando eu escrever a consulta col >= 2, verei Constant Scan no plano de execução real (como quando eu verificaria NULLouSELECT TOP (0)

Mas este não é o caso, opta pela tabela Scan. Eu também preciso ter índice nesta coluna?

No meu teste abaixo eu uso TINYINTcom CHECKrestrição. Tipo definido pelo usuário com base no TINYINTvinculado RULEe no bom e velho BIT.

GO
CREATE TYPE dbo.myBool 
FROM [INT] NOT NULL
GO

CREATE RULE dbo.R_Bool AS @value BETWEEN 0 AND 1
go

EXEC sys.sp_bindrule @rulename = N'R_Bool'
                   , @objname = N'myBool'
GO 


DROP TABLE IF EXISTS dbo.RuleTest
CREATE TABLE dbo.RuleTest
(
    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , oldSchoolBool TINYINT NOT NULL CHECK (oldSchoolBool BETWEEN 0 AND 1)
    , customBool dbo.myBool NOT NULL
    , myBit BIT NOT NULL
)

;WITH tally (n)
AS
(
    SELECT 
        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
    FROM        (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS a(n)
    CROSS JOIN  (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS b(n)
    CROSS JOIN  (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS c(n)

)
INSERT INTO dbo.RuleTest 
(oldSchoolBool, customBool, myBit)
SELECT
    ABS(CHECKSUM(NewId())) % 2 
    ,ABS(CHECKSUM(NewId())) % 2 
    ,ABS(CHECKSUM(NewId())) % 2 
FROM tally t

SET STATISTICS IO ON;

SELECT * FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool IS NULL


SELECT * FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool >=2 

go
SELECT * FROM dbo.RuleTest rt
WHERE rt.customBool >=2 
go
SELECT * FROM dbo.RuleTest rt
WHERE rt.myBit >= 2

SET STATISTICS IO OFF;

Eu vejo um Constant Scan para a verificação NULL e 3 table scans para o resto.

t-sql sql-server-2016

1 respostas

  1. Best Answer

    Consulta 2

    O problema é a parametrização automática .

    No seu caso, a constante 2é substituída por um parâmetro tinyint @1em vez do literal 2- pois esse parâmetro pode ter o valor 0ou 1não seria válido para o otimizador de consulta assumir que a restrição de verificação contradiz isso.

    Você pode usar a consulta a seguir para obter um plano que use a detecção de contradição ( 1=1que impede a parametrização automática) A detecção de contradição ocorre como parte da simplificação (consulte o diagrama de pipeline de otimização aqui).

    SELECT * 
FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool >=2 and 1=1

    o plano resultante é simplificado para uma varredura constante

    insira a descrição da imagem aqui

    Consulta 3

    As regras foram desencorajadas / obsoletas por cerca de 20 anos. 2000 BOL descreve-os como

    um recurso de compatibilidade com versões anteriores... As restrições CHECK são a maneira padrão preferida

    O CREATE RULEtópico afirma

    As regras não se aplicam aos dados já existentes no banco de dados no momento em que as regras são criadas

    Então, imagino que eles nunca sejam confiáveis ​​​​pelo otimizador de consultas, pois é possível fazer o seguinte e ter dados que não aderem à regra

    CREATE TYPE dbo.myBool FROM [INT] NOT NULL

GO

CREATE TABLE dbo.RuleTest
(
  customBool dbo.myBool NOT NULL
)

INSERT INTO  dbo.RuleTest VALUES (10)

go

CREATE RULE dbo.R_Bool AS @value BETWEEN 0 AND 1
GO

EXEC sys.sp_bindrule @rulename = N'R_Bool'
                   , @objname = N'myBool'

    Embora tecnicamente seja possível manter um conceito de regra confiável análogo a restrições confiáveis, não acredito que isso exista.

    Consulta 4

    Você precisa adicionar uma restrição de verificação redundante CHECK (myBit BETWEEN 0 AND 1)ou equivalente se quiser fazer essa detecção de contradição. Mesmo que um bit não anulável possa conter apenas esses dois valores, você não obtém essa contradição detectada sem isso

    • 11

relate perguntas