Disassembling Bit Flag Enumerations in SQL Server

Question

I have an INT column in a SQL Server database which stores a value relating to a bit flag enumeration. For instance, if the enum is:

[Flags()]
public enum UserType
{
    StandardUser = 1,
    Admin = 2,
    SuperUser = 4
}

then the column in SQL Server might hold a value of 5.

What I need to do is select all of the rows from another table holding additional details about the UserType, so using the example of a value of 5, I would want to select the rows from the second table with IDs 1 and 4.

Does anyone know a clever way to break the number up in this manner - ideally the method should be recursive to some degree since this is a very simplified example, and the actual tables/enums are much bigger.

Answer 1

To get all rows for which it is true both that 1 is set, and that 4 is set...

SELECT * FROM UserTable
WHERE userType & 5 = 5

To get all rows for which it is true that at least one of 1 or 4 is set...

SELECT * FROM UserTable
WHERE userType & 5 <> 0

And of course we can combine this with joins:

SELECT Projects.*
FROM Projects JOIN UserTable
ON userID = UserTable.id
WHERE userType & 5 <> 0

Or joining on flags. Where PermissionSets contains flags and users must have all set:

SELECT UserTable.*
FROM UserTable JOIN PermissionSets
ON UserTable.userType & PermissionSets.userType = PermissionSets.userType
WHERE PermissionSets.id = 42

Where PermissionSets contains flags and users must have one set:

SELECT UserTable.*
FROM UserTable JOIN PermissionSets
ON UserTable.userType & PermissionSets.userType <> 0
WHERE PermissionSets.id = 42

To turn on the SuperUser bit for user with id of 93

UPDATE UserTable
SET userType = userType | 4
WHERE id = 93

To turn off the SuperUser bit for user with id of 93

UPDATE UserTable
SET userType = userType & ~4
WHERE id = 93

From the ADO.NET (or whatever) code that is used to deal with this from C# we can pass the relevant UserType value cast to int into a parameter rather than sending 4 or 5 explicitly.

Edit: See also, Bitwise Operators (Transact-SQL)