Sounds like a challenge 🙂

I can be done with FOREIGN KEYS as shown below. (I'm not saying it isn't without its own problems however).

As a side-benefit, this approach also gives you statistics for each bit.

USE tempdb

GO

--DROP TABLE dbo.Flags

GO

-- Table of valid flags

CREATE TABLE dbo.Flags

(

positionINTNOT NULL PRIMARY KEY CLUSTERED CHECK (position BETWEEN -1 AND 15),

bit_valueINTNOT NULL UNIQUE NONCLUSTERED,

CHECK (bit_value = POWER(2, position) OR (position = -1 AND bit_value = 0))

);

GO

-- Add 16 bits plus a placeholder to allow unset bits

INSERTdbo.Flags (position, bit_value)

SELECTTOP (16)

ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS position,

POWER(2, ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1) AS bit_value

FROMmaster.sys.columns

UNIONALL

SELECT-1, 0 -- for unset bits

GO

CREATE TABLE dbo.Data

(

flagsINTNOT NULL,

flag00AS(flags & 1) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag01AS(flags & 2) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag02AS(flags & 4) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag03AS(flags & 8) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag04AS(flags & 16) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag05AS(flags & 32) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag06AS(flags & 64) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag07AS(flags & 128) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag08AS(flags & 256) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag09AS(flags & 512) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag10AS(flags & 1024) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag11AS(flags & 2048) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag12AS(flags & 4096) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag13AS(flags & 8192) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag14AS(flags & 16384) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

flag15AS(flags & 32768) PERSISTED FOREIGN KEY REFERENCES dbo.Flags (bit_value),

)

GO

DELETE dbo.Flags WHERE bit_value = 64-- this flag is not allowed

DELETE dbo.Flags WHERE bit_value = 16384-- this flag is not allowed

GO

INSERTdbo.Data VALUES (63)-- OK

INSERTdbo.Data VALUES (65)-- ERROR (64 not allowed)

INSERTdbo.Data VALUES (16383)-- OK

INSERTdbo.Data VALUES (16386)-- ERROR (16384 not allowed)

GO

DROP TABLE dbo.Flags;