ספט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.

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.

 

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:

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:

 

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.

Let's test it on the numbers 2 and 3:

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.

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.

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.

* 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

>>