There may be situation where we want to store multiple
values against a customer, user etc. Assume
for a customer we need to store whether he owns a House, Car, Bike, Flat, Laptop…….etc.
In normal scenario, what we do is to create separate column for these in the table and save them as Boolean values as below.
2 000010
4 000100
8 001000
16 010000
32 100000
SELECT 'Has Bike' FROM dbo.New_Cuatomer_Data WHERE Owns & 8 = 8
SELECT 'Has Flat' FROM dbo.New_Cuatomer_Data WHERE Owns & 16 = 16
SELECT 'Has Laptop' FROM dbo.New_Cuatomer_Data WHERE Owns & 32 = 32
It will return 2 if 2nd position is on (i.e. if 1)
In normal scenario, what we do is to create separate column for these in the table and save them as Boolean values as below.
By using the & bit operator in SQL we could eliminate
create multiple columns for storing these type of values. Let us see first how
we can store this data by using only a single column.
I believe all we know about binary representation like
Decimal Binary equivalent2 000010
4 000100
8 001000
16 010000
32 100000
The logic here to first represent the below values for each
Has House 2
Has Car 4
Has Bike 8
Has Flat 16
Has Laptop 32
Has Bike 8
Has Flat 16
Has Laptop 32
(You may extent this to store n number using respective
decimal/binary representation like 64,128,256……)
So if we need to
store data for a customer who has house and a flat, what we do is add the numbers
against “Has House” and ”Has Flat” which will give 18 (2(Has House)+16(Has Flat)=18).
Store these values against the customer
in a single column as shown below.
We could compare whether the customer has a house or a flat
using the query below,
SELECT 'Has House' FROM dbo.New_Cuatomer_Data
WHERE Owns &
2 = 2
SELECT 'Has Car' FROM dbo.New_Cuatomer_Data
WHERE Owns &
4 = 4 SELECT 'Has Bike' FROM dbo.New_Cuatomer_Data WHERE Owns & 8 = 8
SELECT 'Has Flat' FROM dbo.New_Cuatomer_Data WHERE Owns & 16 = 16
SELECT 'Has Laptop' FROM dbo.New_Cuatomer_Data WHERE Owns & 32 = 32
How does it work?
The logic works here is that, the & operator checks whether
the bit positions specified after the & operator in the query is on or not(1
or 0) .For example in the case of 18, it will binary represented as below.
Hence for
SELECT Owns & 2 FROM dbo.New_Cuatomer_Data
It will return 2 if 2nd position is on (i.e. if 1)
Binary Bit Positions
|
32
|
16
|
8
|
4
|
2
|
1
|
Decimal equivalent 18
|
0
|
1
|
0
|
0
|
1
|
0
|
