t6P7Sv9m

1GO
2CREATE TYPE dbo.myBool 
3FROM [INT] NOT NULL
4GO
5
6CREATE RULE dbo.R_Bool AS @value BETWEEN 0 AND 1
7go
8
9EXEC sys.sp_bindrule @rulename = N'R_Bool'
10                   , @objname = N'myBool'
11GO 
12
13
14DROP TABLE IF EXISTS dbo.RuleTest
15CREATE TABLE dbo.RuleTest
16(
17    Id INT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
18    , oldSchoolBool TINYINT NOT NULL CHECK (oldSchoolBool BETWEEN 0 AND 1)
19    , customBool dbo.myBool NOT NULL
20    , myBit BIT NOT NULL
21)
22
23;WITH tally (n)
24AS
25(
26    SELECT 
27        ROW_NUMBER() OVER (ORDER BY (SELECT NULL))
28    FROM        (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS a(n)
29    CROSS JOIN  (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS b(n)
30    CROSS JOIN  (VALUES (0), (0), (0), (0), (0), (0), (0), (0), (0), (0))   AS c(n)
31
32)
33INSERT INTO dbo.RuleTest 
34(oldSchoolBool, customBool, myBit)
35SELECT
36    ABS(CHECKSUM(NewId())) % 2 
37    ,ABS(CHECKSUM(NewId())) % 2 
38    ,ABS(CHECKSUM(NewId())) % 2 
39FROM tally t
40
41SET STATISTICS IO ON;
42
43SELECT * FROM dbo.RuleTest rt
44WHERE rt.oldSchoolBool IS NULL
45
46
47SELECT * FROM dbo.RuleTest rt
48WHERE rt.oldSchoolBool >=2 
49
50go
51SELECT * FROM dbo.RuleTest rt
52WHERE rt.customBool >=2 
53go
54SELECT * FROM dbo.RuleTest rt
55WHERE rt.myBit >= 2
56
57SET STATISTICS IO OFF;