Integer Based Bit Manipulation - SQL

Introduction

Hopefully my topic isn't too far off.  In this post I'm going to cover what I like to think of as easy bit manipulation.  Well, it's easier anyway.  The basic concept is that an integer is comprised of bits.  Integers are easier to look at and play with than bits, especially if we're storing bit values in SQL and we want to update them...  Using logical bitwise operators, we can query one integer with another to see which bits they have in common.  When performing the query, if one value is strictly a bit, we can see if that bit is 'on' in the second value, which is a sum of one or more bits.  Alternatively, we can test two summed bits to see if one is a union of the other.  To me this means all of one exists completely in the other.

I plan to post a C# extension method version of the SQL functions below in a day or two.  Since I'm not using VS.Net 2008 full time yet, I need to convert what I have to extension methods.  Well, I guess I don't need to, but since I think extension methods are cool, I'm going to:)

Example

I'll start with a security example, but the context can be anything at all.  We need two values, one is the test bit and one is a sum of bits.

Since we're doing permissions, our test bits might look like the following.

• CanView = 1
• CanEdit = 2
• CanSubmit = 4
• CanApprove = 8
• CanReject = 16

What about that sum of bits?  Sure, lets say we have an administrator with all bits.  Their sum of bits would be 1 + 2 + 4 + 8 + 16 = 31.  Perhaps we have a contributor, they can only view edit and submit, they would have 1 + 2 + 4 = 7.

Ok, that was easy, let's say that users with Admin permissions have a 31 and contributors have a 7.  Suddenly we have the need to perform a check, can the user Approve? Can the user Edit?

Let's see, we need another table to consider this, let's examine the bits of CanApprove and CanEdit.

So, can the Admin approve or edit?  How about the Contributor?

I hope this is legible and understandable so far.  This last table is trying to line up the summed bits of the admin and contributors juxtaposed with the bits of CanApprove and CanEdit to simplify comparison.  The green shows a match and the red shows a miss.  Above we can see that the integer value for admin has both the CanApprove and CanEdit bits.  At the same time, we see that the Contributor bits do not contain the CanApprove bit, but they do contain the CanEdit bits.

Pretty exciting stuff isn't it :)

Uses

Hopefully at this point, I have successfully demonstrated how integers can be used to define flags and then store them in aggregate.  Where I work we primarily use them in two instances.

First in granular page permissions such as the example above, allowing functionality based on permissions.  After authorizing the user on a page, we then enable UI components based on the bits of the component compared to the bits of the user.

Next, we use this as a way to store multiple bit fields.  Some might warn about performance, no we haven't tested performance here, because we haven't seen an issue.  For example, let's say we send out a form with multiple checkboxes on it.  If we have eight bit fields on a form, instead of persisting to eight bit columns we persist to one integer called FormBits.  When we need to know if something was checked or not, we simply examine the integer for the bits we need.

Code

Enough talk, what about this SQL stuff?  Below are methods to test and read bit values SQL.  There are some comments in the code, but I'm not going to elaborate on the bitwise operators.

SQL

1. CREATE FUNCTION [Utilities].[SetBit]
2. (
3.   -- the bits to check as an integer
4.   @iBits INT
5.   -- this bit to test
6.   -- this must be a bit value
7.   -- e.g. 1, 2, 4, 8, 16, 32, 64, etc
8.   -- non bit values will produce unknown results
9.   -- e.g. 3, 5, 6, 7, 9, 10, 11, etc
10.   ,@iTestBit INT
11.   -- should the bit be on or off
12.   ,@iBitState BIT
13. )
14. RETURNS INT
15. AS
16. BEGIN
17.   DECLARE @BitState BIT;
19.   -- we are validating that the testbit paramater is a bit
20.   -- if it isn't, we return null
21.   IF (Utilities.IsBit(@iTestBit) = 0)
22.     RETURN NULL;
24.   -- determine the current state of the test bit
25.   SET @BitState = Utilities.ReadBit(@iBits, @iTestBit);
27.   -- leave if the value is correct
28.   IF (@BitState = @iBitState)
29.     RETURN @iBits;
31.   IF (@BitState = 0 AND @iBitState = 1)
32.     RETURN @iBits + @iTestBit;
34.   RETURN @iBits - @iTestBit;
35. END;
36.  
37. CREATE FUNCTION [Utilities].[ReadBit]
38. (
39.   -- the bits to check as an integer
40.   @iBits INT
41.   -- this bit to test
42.   -- this must be a bit value
43.   -- e.g. 1, 2, 4, 8, 16, 32, 64, etc
44.   -- non bit values will produce unknown results
45.   -- e.g. 3, 5, 6, 7, 9, 10, 11, etc
46.   ,@iTestBit INT
47. )
48. RETURNS BIT
49. AS
50. BEGIN
51.   DECLARE @ComparisonResults INT;
53.   -- we are validating that the testbit paramater is a bit
54.   -- if it isn't, we return null
55.   IF (Utilities.IsBit(@iTestBit) = 0) BEGIN
56.     RETURN NULL;
57.   END;
59.   -- bitwise and the bits and the test bit
60.   SET @ComparisonResults = @iBits & @iTestBit;
62.   -- if the result equals the test bit, the bit is present
63.   IF (@ComparisonResults = @iTestBit) BEGIN
64.     RETURN 1;
65.   END;
66.   ELSE BEGIN
67.     RETURN 0;
68.   END;
70.   -- this return is required by the function
71.   -- it should never be hit
72.   RETURN 0;
73. END;
75. CREATE FUNCTION [Utilities].[IsBit]
76. (
77.   @iTestBit INT
78. )
79. RETURNS BIT
80. AS
81. BEGIN
82.   -- This function will take an integer and return true if it is a bit value, i.e. 1, 2, 4, 8
83.   DECLARE @Result BIT;
85.   IF @iTestBit = 1
86.     -- Special case of TestBit = 1
87.     SET @Result = 1;
88.   ELSE IF ROUND(LOG(@iTestBit)/LOG(2),1) = LOG(@iTestBit)/LOG(2)
89.     -- if the LOG Base 2 of the TestBit value is an INTEGER, you have found 2, 4, 8, 16
90.     SET @Result = 1;
91.   ELSE
92.     SET @Result = 0;
94.   RETURN @Result;
95. END;
97. CREATE FUNCTION Utilities.IsBitUnion
98. (
99.     -- the bits to check as an integer
100.     @iBits INT
101.     -- this bits to test
102.     ,@iTestBits INT
103. )
104. RETURNS BIT
105. AS
106. BEGIN
107.     DECLARE @ComparisonResults INT;
108.     -- bitwise and the bits and the test bits
109.     SET @ComparisonResults = @iBits & @iTestBits;
110.  
111.     -- if the input bits equals the test bits, we have a union
112.     IF (@ComparisonResults = @iTestBits) BEGIN
113.         RETURN 1;
114.     END;
115.  
116.     RETURN 0;
117. END;
119. -- CanView = 1
120. -- CanEdit = 2
121. -- CanSubmit = 4
122. -- CanApprove = 8
123. -- CanReject = 16
124. -- Admin = 31
125. -- Contributor = 7
126. SELECT Utilities.ReadBit(31, 8) -- 1 : Admin has CanApprove
127. SELECT Utilities.ReadBit(31, 2) -- 1 : Admin has CanEdit
128. SELECT Utilities.IsBitUnion(31, 10) -- 1 : Admin has CanApprove & CanEdit
129. SELECT Utilities.ReadBit(7, 8) -- 0 : Contributor does not have CanApprove
130. SELECT Utilities.ReadBit(7, 2) -- 1 : Contributor has CanEdit
131. SELECT Utilities.IsBitUnion(7, 10) -- 0 : Contributor does not have CanApprove & CanEdit
132. SELECT Utilities.SetBit(7, 8, 1) -- 15 : Add CanApprove to Contributor
133. SELECT Utilities.ReadBit(15, 8) -- 1 : Contributor has CanApprove
134. SELECT Utilities.IsBitUnion(15, 10) -- 1 : Contributor has CanApprove & CanEdit

The IsBit function can be used as a constraint on columns that hold the individual bits that make up permissions and to validate that the test bit input to the ReadBit function is a bit.  If a non-bit were passed to readbit, bad things happen.

Thank you for reading my post, any and all feedback is encouraged!
Ben

P.S. As a quick SQL rant/question, what is up with synonyms not being allowed for schema bound objects?  Where I work, we have a shared database that multiple project databases reference in one way or another.  The Utilities.IsBit function above is employed in databases to ensure that any configured bit values are bits.  Makes sense right?  Well, since we cannot use a schema bound object in a check constraint, we have to have multiple copies of the function.  This is also the case with other entities that we would otherwise use a synonym to reference.  Is it me, or is this an unnecessary inconvenience?