為什麼 SQL Optimizer 不使用我的約束？

我想創建一個帶有NOT NULLbool 列的表。

我使用TINYINTwithCHECK約束BETWEEN 0 and 1。約束是新的，因此是可信的

現在我希望 SQL 優化器現在知道該列只能是 0 和 1，所以當我編寫查詢時，col >= 2我會在實際執行計劃中看到 Constant Scan（就像我檢查NULL或SELECT TOP (0)

但事實並非如此，它選擇了表掃描。我是否還需要在此列上有索引？

在下面的測試中，我使用TINYINT約束CHECK。基於TINYINTwith boundRULE和 good old的使用者定義類型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 個用於其餘部分的表掃描。

查詢 2

問題是自動參數化。

在您的情況下，常量2被替換為 tinyint 參數@1而不是文字2- 因為此參數可能具有值0，或者1查詢優化器假定檢查約束與此相矛盾是無效的。

您可以使用以下查詢來獲取使用矛盾檢測的計劃（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)如果您希望它執行此矛盾檢測，則需要添加冗餘檢查約束或等效項。即使一個不可為空的位只能保存這兩個值，如果沒有它，您也不會檢測到這種矛盾

引用自：https://dba.stackexchange.com/questions/235172