ספט6
Written by:
ronen ariely
06/09/2015 14:03
introduction
Bitwise Operators performs wise bit by bit manipulations between two expressions of types integers or binaries (binary, bit, int, smallint, tinyint, varbinary, bigint). There are three Bitwise Operators in T-SQL which includes: & (Bitwise AND), | (Bitwise OR), ^ (Bitwise Exclusive OR). In this short post we will explain the behavior of each of these operators by following a simple practical examples.
Wise bit by bit manipulations, meaning that the value is not calculating as one, but each bit from the first expression is calculates against the bit in the other expression that is placed in the same place. The calculations done on the binary data bit by bit.
The bitwise logical And in the result are set to 1 if and only if both bits in the input expressions have a value of 1.
The bitwise logical Or in the result are set to 1 if either or both bits in the input expressions have a value of 1.
The bitwise logical Xor in the result are set to 1 if either but not both bits in the input expressions have a value of 1.
Bitwise in practice
Preparation
I will create two functions. The first function is basically for learning. It displays the information in more suitable way for our discussion. The second function is recommended on production, if you need to convert numbers from base ten to base two.
Function 01: Explor_Int_As_Bin_String
Let's create new function which parse integers into base 2 string. To make it easier to understand, I returns each bit separately in different column, and in the last column I returns the whole number.
-- Explor INTEGER as BINARY USING Bitwise OPERATOR
IF OBJECT_ID (N
'Explor_Int_As_Bin_String'
, N
'IF'
)
IS
NOT
NULL
DROP
FUNCTION
Explor_Int_As_Bin_String
GO
CREATE
FUNCTION
Explor_Int_As_Bin_String (@I
BIGINT
)
RETURNS
TABLE
AS
RETURN
(
-- If we are not sure what is the length of the result
-- Then we can first find how meny charecters our result needs
-- using this example, and next loop from 1 to @BinaryLanth
-- DECLARE @BinaryLanth INT = FLOOR(LOG ( @N , 2))
-- Od we can just assume that our data is not more then 7 charecters and work directly without looping
SELECT
@I
as
Input
,
cast
(@I & 64
as
bit
)
as
bit7
,
cast
(@I & 32
as
bit
)
as
bit6
,
cast
(@I & 16
as
bit
)
as
bit5
,
cast
(@I & 8
as
bit
)
as
bit4
,
cast
(@I & 4
as
bit
)
as
bit3
,
cast
(@I & 2
as
bit
)
as
bit2
,
cast
(@I & 1
as
bit
)
as
bit1
,
cast
(
cast
(@I & 64
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 32
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 16
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 8
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 4
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 2
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 1
as
bit
)
as
CHAR
(1))
as
binary_string
)
GO
Function 02: Ariely_Int2Bin_Fn
let's create a simple scalar function to convert integer to base two number. This is similar to the previous function, but it return only the base two string.
-- CONVERTING FROM INTEGER TO BINARY USING Bitwise OPERATOR
IF OBJECT_ID (N
'Ariely_Int2Bin_Fn'
, N
'IF'
)
IS
NOT
NULL
DROP
FUNCTION
Ariely_Int2Bin_Fn
GO
CREATE
FUNCTION
Ariely_Int2Bin_Fn (@I
BIGINT
)
RETURNS
CHAR
(7)
AS
BEGIN
-- If we are not sure what is the length of the result
-- Then we can first find how meny charecters our result needs
-- using this example, and next loop from 1 to @BinaryLanth
-- DECLARE @BinaryLanth INT = FLOOR(LOG ( @N , 2))
-- Od we can just assume that our data is not more then 7 charecters and work directly without looping
RETURN
cast
(
cast
(@I & 64
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 32
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 16
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 8
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 4
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 2
as
bit
)
as
CHAR
(1))
+
cast
(
cast
(@I & 1
as
bit
)
as
CHAR
(1))
END
GO
Example 01: manually calculating Bitwise AND operator
In order to calculate the Bitwise AND for the integers 2 and 3 (2 & 3), we need to convert them into base two numbers. The integer 2 in binary is 10, and the integer 3 is 11. To make it easier to understand our calculation we displays one beneath the other:
| Second place | First Place |
The number 2 In base two | 1 | 0 |
The number 3 In base two | 1 | 1 |
Therefore, Bitwise operator on 2 and 3 will calculate the operator separately on each bit according to its place. First bit for 2 is 0, and first bit for 3 is 1, so the Bitwise AND operator return 0 for the first place. In the same way we can calculates the second bit in the result. Since both has 1 in second place, the AND operator return 1 for the second place. The result is 10, which in base ten is 2.
| Second place | First Place |
The number 2 In base two | 1 | 0 |
The number 3 In base two | 1 | 1 |
Result 2 & 3 | 1 | 0 |
Example 2: manually calculating all Bitwise operators
Now we can check how the numbers 2 and 3 looks like, which is the example above:
select
*
from
Explor_Int_As_Bin_String(2)
select
*
from
Explor_Int_As_Bin_String(3)
GO
Now we can calculate each of the Bitwise operators bit by bit:
the result of 2 & 3 is:
bit 1: 0&1 = 0
bit 2: 1&1 = 1
bit 3: 0&0 = 0
bit 4: 0&0 = 0
...
the result of 2 | 3 is:bit 1: 0|1 = 1 bit 2: 1|1 = 1 bit 3: 0|0 = 0 bit 4: 0|0 = 0 ... | the result of 2 ^ 3 is:bit 1: 0^1 = 1 bit 2: 1^1 = 0 bit 3: 0^0 = 0 bit 4: 0^0 = 0 ... |
You can execute these querye in order to examine the results above:
select
*
from
Explor_Int_As_Bin_String(2&3)
select
*
from
Explor_Int_As_Bin_String(2|3)
select
*
from
Explor_Int_As_Bin_String(2^3)
GO
Example 03: Executing all Bitwise operation dynamically
Now we can create simple stored procedure that get 2 BIGINT as input, and return the results of each of the Bitwise operators + the base two number. The information is orders one beneath the others to make it simple to understand the relation between each bit in the input to each bit in the results.
ALTER
PROCEDURE
CalculateBitwise (@A
BIGINT
, @B
BIGINT
)
as
BEGIN
SELECT
'A'
AS
[Operator] , @A
AS
[VALUE], dbo.Ariely_Int2Bin_Fn(@A)
AS
[Base two]
UNION
ALL
SELECT
'B'
AS
[Operator] , @B
AS
[VALUE], dbo.Ariely_Int2Bin_Fn(@B)
AS
[Base two]
UNION
ALL
SELECT
'A And B'
AS
[Operator] , (@A & @B)
AS
[VALUE], dbo.Ariely_Int2Bin_Fn((@A & @B))
AS
[Base two]
UNION
ALL
SELECT
'A Or B'
AS
[Operator] , (@A | @B)
AS
[VALUE], dbo.Ariely_Int2Bin_Fn((@A | @B))
AS
[Base two]
UNION
ALL
SELECT
'A Xor B'
AS
[Operator] , (@A ^ @B)
AS
[VALUE], dbo.Ariely_Int2Bin_Fn((@A ^ @B))
AS
[Base two]
END
GO
Let's test it on the numbers 2 and 3:
execute
dbo.CalculateBitwise 2,3
GO
You are welcome to execute the above SP using different inputs, till you understand how Bitwise is working :-)
Bitwise in real life
We already show in the the above code how we use Bitwise in order to create a function that convert numbers from base 10 (integers) to numbers in base two.
In one of my previous articles that I posted at Microsoft TechNet WIKI, I showed how we can Represent a List of elements (values) Using a Single Value. Several of the solutions that I showed, based on storing an integer number, which its binary value Represents the List of the Elements. Using the built-in Bitwise operators, we can manipulate and interpreters the integer directly.
As I mentioned in the Linux File Permissions is a good example of using one integer which actually represents 3 security privileges. Each file or directory, has three permissions rules: Read (r), Write (w), Execute (x). We can represent each permission as 1 if the user has this permission and 0 if not. Therefore, the user permissions is a list of zero and ones. In other words it is a number on base 2. Lets says that read permission is the first character in the binary number, Write is the second character, and execute is the third. There fore the user that has permission 000 has no permission while the user that has the permission 111 has full permission (read+write+execute). in order to store the permission we use simple integer, therefore permission 000 is stored as 0, permission 010 stored as 2, permission 011 stored as 3, and so on.
Question 01: In the database we see a user with the permission 7. Which permissions hides in this number?!?
Answer 01: we can use my above function using Bitwise, in order to convert the number base ten into a number in base two and the answer will be 111, which mean that the user has full permissions.
Question 02: The user has permission 5. Is that mean that he allowed to read?
Answer 02: We can use the same function in order to convert the integer into 101. It is easy to see that the user has permissions for the first and last option (read and execute, but not write). but in order to show list of permissions that the user has, we can do it directly.
declare
@UserPermissions
INT
= 5
select
@UserPermissions & 1
as
[
Read
],
@UserPermissions & 2
as
[Write],
@UserPermissions & 4
as
[
Execute
]
GO
Each column in the solution that is not 0 mean that the user has that permission:
Question 03: How can we add both read and write permissions to all users ?
Answer 03: There is no need for complex calculation. permissions read+write represented by the integer 3. We can simply use Bitwise OR on the user current permission with the new permission that we want to add 3.
create
table
Users (
id
INT
,
UserPermissions
INT
)
truncate
table
Users
insert
Users (id,UserPermissions)
values
(1,1),
(2,2),
(3,3),
(4,4),
(5,5),
(6,6),
(7,7)
GO
-- current permissions
select
id,
UserPermissions & 1
as
[
Read
],
UserPermissions & 2
as
[Write],
UserPermissions & 4
as
[
Execute
]
from
Users
GO
-- Update permissions
update
Users
SET
UserPermissions = UserPermissions | 3
GO
-- New permissions
select
id,
UserPermissions & 1
as
[
Read
],
UserPermissions & 2
as
[Write],
UserPermissions & 4
as
[
Execute
]
from
Users
GO
Question 04: How can we remove write permission to all users ?
Answer 04: In the same way we added permissions using Bitwise Or, we can remove permissions directly, using the Bitwise AND operator. The basic idea is to use AND operator with a value that includes all other permissions except permission 2, which is write. If the user already had a specific permission for example read and we user AND with another oerator that include that permission then it will stay 1, and if it did not the permission that it will stay 0 since 1 and 0 return 0. But the new operator dose not include permission 2 so it is not metter what the permission was bvefore 1 AND 0 return the same as 1 AND 0, which is 0.
-- Update permissions: remove Wrie, by using AND with all other permissions which are 1+4 = 5
update
Users
SET
UserPermissions = UserPermissions & 5
GO
-- New permissions
select
id,
UserPermissions & 1
as
[
Read
],
UserPermissions & 2
as
[Write],
UserPermissions & 4
as
[
Execute
]
from
Users
GO
* In this example we used simple list of three permissions, but this logic can serve us in much more complex lists as mentioned in the TechNet WIKI article I mentioned above.
Summary and Conclusions
Bitwise Operators are built-in in SQL Server and can be very valuable tools, especially when we work with numbers on base two or our logic is based on base two. In this short post we explained the basic behavior of each of these operators and show some practical examples. The ball is in your court Now :-). Are your solutions can be improved using Bitwise?
Resources and more information
>> SQL Server: Representing List of Values Using a Single Value
http://social.technet.microsoft.com/wiki/contents/articles/28159.sql-server-representing-list-of-values-using-a-single-value.aspx
>>