Rrerku

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.

Query 2

The issue is Auto parameterization not trivial plan. Contradiction detection happens as part of simplification before the trivial plan stage (see the Optimization Pipeline diagram here).

In your case the constant 2 gets replaced with a tinyint parameter @1 rather than the literal 2 - as this parameter could have the value 0 or 1 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 trivial plan that does use the contradiction detection (the 1=1 prevents the Auto parameterization).

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

Query 3

Rules have been deprecated for about 20 years. The CREATE RULE topic states

Rules do not apply to data already existing in the database at the
time the rules are created

So 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

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'

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

Because your query is trivially simple, hitting a 1,000-row table (super low cost), and it's getting trivial optimization.

To get constraint checking, your query needs to get past the trivial optimization phase. One way to do it is to add a subquery:

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

SELECT * FROM dbo.RuleTest rt
WHERE rt.oldSchoolBool >=2 
 AND 1 = (SELECT 1);

Because the second query bypasses trivial optimization, it gets optimizations for stuff like constraints:

To see when your query is getting trivial optimization, right-click on the SELECT operator in the query plan, and click Properties. In your original queries, the optimization level shows TRIVIAL:

In the real world, trivial optimization queries usually aren't your biggest bottleneck - but when you do see these, not only are constraints getting bypassed, but also index recommendations, indexed view utilization, parallelism, and more.