Mdtryhht

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 discouraged/deprecated for about 20 years. 2000 BOL describes them as

a backward-compatibility feature ... CHECK constraints are the
preferred, standard way

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