SQL Server Bitwise Processing like C# Enum Flags

Question

How can one use in SQL Server the processing of the Flags such as on enums in C#?

---

For example, how would one return a list of users that are part of a list or conditions like so:

ConditionAlpha = 2 ConditionBeta  = 4 ConditionGamma = 8 

...

Then there will be users with some of these conditions against them like so:

User1: 6 (conditions Alpha and Beta) User2: 4 (condition Beta) User3: 14 (conditions Alpha, Beta and Gamma) 

...

We want to be able to do a query where we say get all users with the first condition Alpha and in this scenario it would return users 1 and 3 even though they have other conditions as well.

Answer 1

The bitwise operator for checking whether a flag is set in SQL is &. The WHERE clause needs to evaluate to a BOOLEAN expression, like this:

create table #temp (id int, username varchar(20), flags int)  insert into #temp values (1, 'User1', 6 /* (2 | 4) */), (2, 'User2', 4), (3, 'User3', 14 /* (2 | 4 | 8) */)  declare @ConditionOne int = 2  select * from   #temp where  flags & @ConditionOne <> 0  declare @ConditionTwo int = 4  select * from   #temp where  flags & @ConditionTwo <> 0  declare @ConditionThree int = 8  select * from   #temp where  flags & @ConditionThree <> 0  drop table #temp 

These queries return the following resultsets:

id          username             flags ----------- -------------------- ----------- 1           User1                6 3           User3                14  id          username             flags ----------- -------------------- ----------- 1           User1                6 2           User2                4 3           User3                14  id          username             flags ----------- -------------------- ----------- 3           User3                14 

Answer 2

While the bitwise operator suggested by James will work, it will not be very performant in a relational database, especially when you try to scale to millions of records. The reason is that functions in the where clause are not sargable (they prevent an index seek).

What I would do would be create a table which contains all possible combinations of flags and conditions, which will enable an index seek on the condition.

Populate FlagConditions. I used a single (tinyint). Should you need more Flags, you should be able to expand on this approach:

CREATE TABLE FlagConditions (       Flag TINYINT     , Condition TINYINT     , CONSTRAINT Flag_Condition PRIMARY KEY CLUSTERED (Condition,Flag) );  CREATE TABLE #Flags (       Flag TINYINT IDENTITY(0,1) PRIMARY KEY CLUSTERED     , DummyColumn BIT NULL); GO  INSERT #Flags         ( DummyColumn ) SELECT NULL; GO 256  CREATE TABLE #Conditions(Condition TINYINT PRIMARY KEY CLUSTERED);  INSERT #Conditions ( Condition )     VALUES  (1),(2),(4),(8),(16),(32),(64),(128);  INSERT FlagConditions ( Flag, Condition )             SELECT     Flag, Flag & Condition     FROM #Flags f     CROSS JOIN #Conditions c     WHERE Flag & Condition <> 0;  DROP TABLE #Flags; DROP TABLE #Conditions; 

Now you can use the FlagConditions table any time you need to efficiently seek on an enum bitwise condition:

DECLARE @UserFlags TABLE (Username varchar(10), Flag tinyint);  INSERT @UserFlags(Username, Flag)     VALUES ('User1',6),('User2',4),('User3',14);  DECLARE @Condition TINYINT = 2;  SELECT u.* FROM @UserFlags u INNER JOIN FlagConditions fc ON u.Flag = fc.Flag WHERE fc.Condition = @Condition; 

This returns:

Username   Flag ---------- ---- User1      6 User3      14 

Your DBA will thank you for going this set oriented route.