I want to create a table with a NOT NULL
bool column.
I use TINYINT
with CHECK
constraint BETWEEN 0 and 1
. The constraint is new and thus trusted
Now I would expect that SQL optimizer now knows that this column can only be 0 and 1 so when I write query col >= 2
I will see Constant Scan in actual execution plan (like when I would when check for NULL
or SELECT TOP (0)
But this is not the case, it opts for the table Scan. Do I also need to have index on this column?
In my test below I use TINYINT
with CHECK
constraint. User Defined Type based on TINYINT
with bound RULE
and 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;
I see one Constant Scan for the NULL check and 3 table scans for the rest.
Best Answer
Query 2
The issue is Auto parameterization.
In your case the constant
2
gets replaced with a tinyint parameter@1
rather than the literal2
- as this parameter could have the value0
or1
it wouldn't be valid for the query optimiser to assume the check constraint contradicts this.You can use the following query to get a plan that does use the contradiction detection (the
1=1
prevents the Auto parameterization) The contradiction detection then happens as part of simplification (see the Optimization Pipeline diagram here).the resulting plan is simplified to a constant scan
Query 3
Rules have been discouraged/deprecated for about 20 years. 2000 BOL describes them as
The
CREATE RULE
topic statesSo I imagine these are never trusted by the query optimiser as it is possible to do the following and have data that does not adhere to the rule
Whilst technically it might be possible to maintain a trusted rule concept analogous to trusted constraints I don't believe this exists.
Query 4
You need to add a redundant check constraint on
CHECK (myBit BETWEEN 0 AND 1)
or equivalent if you want it to do this contradiction detection. Even though a not nullable bit can only hold those two values you don't get this contradiction detected without that