为什么SQL优化器不使用my约束？

Question

我想要创建一个带有NOT NULL bool列的表。

我使用TINYINT和CHECK约束BETWEEN 0 and 1。约束是新的，因此是可信的。

现在，我希望SQL优化器现在知道这个列只能是0和1，所以当我编写查询col >= 2时，我将看到实际执行计划中的常量扫描(就像在检查NULL或SELECT TOP (0)时一样)。

但事实并非如此，它选择了表格扫描。我也需要在这个列上索引吗？

在下面的测试中，我使用了带有TINYINT约束的CHECK。用户定义的类型基于TINYINT与绑定的RULE和良好的旧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;

我看到NULL检查的一个常量扫描，其余的3个表扫描。

Answer 1

查询2

问题是自动参数化。

在您的示例中，常量2被替换为tinyint参数@1而不是文字2 --因为这个参数可能具有值0或1，所以查询优化器假定check约束与此矛盾是无效的。

您可以使用下面的查询来获得一个确实使用矛盾检测( 1=1阻止自动参数化)的计划，然后作为简化的一部分进行矛盾检测(参见这里的优化管道图)。

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

生成的计划被简化为常量扫描。

查询3

大约20年来，人们一直不鼓励[废除]规则。2000 BOL将其描述为

向后兼容功能.检查约束是首选的标准方法。

CREATE RULE主题状态

规则不适用于在创建规则时数据库中已经存在的数据。

因此，我认为查询优化器从来不信任这些数据，因为可以执行以下操作，并且有不遵守规则的数据

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'

虽然从技术上讲，可能有可能保持类似于受信任约束的可信规则概念，但我不认为这是存在的。

查询4

您需要在CHECK (myBit BETWEEN 0 AND 1)上添加一个冗余的检查约束，或者，如果您希望它执行这种矛盾检测，则需要添加一个等价的检查约束。即使不能为空的位只能保存这两个值，但如果没有该值，则无法检测到这种矛盾。