Bitwise Operators!

The outcome of the following 2 simple queries
---
PRINT 15 & 75
PRINT 15 | 75
---
are 11 & 79 respectively. Can someone explain how does SQL Server
compute these values? I went through the topic 'Bitwise Operators' in
BOL but couldn't exactly comprehend the explanation & that's why I am
posting my query here.
Thanks,
Arpan"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124150426.712216.65110@.g14g2000cwa.googlegroups.com...
> The outcome of the following 2 simple queries
> ---
> PRINT 15 & 75
> PRINT 15 | 75
> ---
> are 11 & 79 respectively. Can someone explain how does SQL Server
> compute these values? I went through the topic 'Bitwise Operators' in
> BOL but couldn't exactly comprehend the explanation & that's why I am
> posting my query here.
> Thanks,
> Arpan
>
Bitwise OR : If either bit value is 1, then the resulting bit is 1, else 0
Bitwise AND : If either bit value is 0, then the resulting bit is 0, else 1
OR:
1001011 = 75
0001111 = 15
--
1001111 = 79
AND:
1001011 = 75
0001111 = 15
--
0001011 = 11|||15 & 75 =
(in binary)
00001111 &
01001011
--
00001011 = 11 (in decimal)
15 | 75 =
(in binary)
00001111 |
01001011
--
01001111 = 79 (in decimal)
SQL Server, from memory, just calculates these kind of binary ANDs and
ORs using twos complement arithmetic.
Hope this helps.
*mike hodgson*
blog: http://sqlnerd.blogspot.com
Arpan wrote:

>The outcome of the following 2 simple queries
>---
>PRINT 15 & 75
>PRINT 15 | 75
>---
>are 11 & 79 respectively. Can someone explain how does SQL Server
>compute these values? I went through the topic 'Bitwise Operators' in
>BOL but couldn't exactly comprehend the explanation & that's why I am
>posting my query here.
>Thanks,
>Arpan
>
>|||Thanks, Chris, for your input but to be honest, I couldn't follow how
did you arrive at the values 1001111 & 0001011? What did you do with
the binary values of 75 & 15 to get to 79 and 11 & their respective
binary values? Please explain me this.
Also how does one find the binary value of a decimal number (of course,
other than using the Windows Scientific Calculator)? Is there any
built-in SQL Server function to do so?
Thanks once again & thanks to Mike as well,
Regards,
Arpan|||The binary number system: each bit represents a power of 2.
0 1
1 2
2 4
3 8
4 16
5 32
6 64
7 128
8 256
9 512
...
For fractions, each bit past the binary point:
-1 .5
-2 .25
-3 .125
-4 .0625
-5 .03125
-6 .015625
-7 .0078125
-8 .00390625
-9 .001953125
...
Each binary number is a combination of bits.
75decimal = 1001011
15decimal = 0001111
A bitwise AND performs a binary AND operation using the following table:
A B | A & B
--
0 0 | 0
0 1 | 0
1 0 | 0
1 1 | 1
for each bit in a binary number, thus
75decimal = 1001011
15decimal = 0001111
--
75 & 15 = 0001011 = 11 decimal
Notice that for each bit position which is 1 in the result, both bits in the
same position of each operand is one.
A bitwise OR performs a binary OR operation using the following table:
A B | A | B
--
0 0 | 0
0 1 | 1
1 0 | 1
1 1 | 1
for each bit in a binary number, thus
75decimal = 1001011
15decimal = 0001111
--
75 | 15 = 1001111 = 79 decimal
Notice that for each bit position which is 1 in the result, at least one bit
from the same position of either operand is one.
"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124153765.707009.29780@.g49g2000cwa.googlegroups.com...
> Thanks, Chris, for your input but to be honest, I couldn't follow how
> did you arrive at the values 1001111 & 0001011? What did you do with
> the binary values of 75 & 15 to get to 79 and 11 & their respective
> binary values? Please explain me this.
> Also how does one find the binary value of a decimal number (of course,
> other than using the Windows Scientific Calculator)? Is there any
> built-in SQL Server function to do so?
> Thanks once again & thanks to Mike as well,
> Regards,
> Arpan
>|||"Arpan" <arpan_de@.hotmail.com> wrote in message
news:1124153765.707009.29780@.g49g2000cwa.googlegroups.com...
> Thanks, Chris, for your input but to be honest, I couldn't follow how
> did you arrive at the values 1001111 & 0001011? What did you do with
> the binary values of 75 & 15 to get to 79 and 11 & their respective
> binary values? Please explain me this.
> Also how does one find the binary value of a decimal number (of course,
> other than using the Windows Scientific Calculator)? Is there any
> built-in SQL Server function to do so?
> Thanks once again & thanks to Mike as well,
> Regards,
> Arpan
>
[url]http://www.math.grin.edu/~rebelsky/Courses/152/97F/Readings/student-binary.html[/u
rl]|||1) These are not queries; they are print statements..
2) Good programmers do not do proprietary, non-realtional, low-level
bits and bytes stuff in SQL.
You keep posting requests for kludges. Perhaps you ought to take the
time to learn RDBMS, data modeling, etc. Remember that it takes six
years to become a Union Journey Carpenter in New York State. Do not
expect to learn all of this by posting to Newsgroups -- all you will do
is collect kludges and become a danger to your employers. .|||Binary arithmetic - All right! Now, if someone will start a thread
about zone and digit punches, I will have come full circle.
Actually, this reminds me of my favorite puzzle only a computer g
(like me) could love. I stole it from Issac Asimov. It was in one of
his Black Widowers mysteries.
Given: Halloween = Christmas
Construct a proof.
----
I will shorten it.
Halloween = Christmas
December 25 = October 31
25dec = 31oct
Payson|||>> Given: Halloween = Christmas Construct a proof.
You already gave the hint there with the author. From The Family Man? For
those who are unfamiliar:
It is basically the distinction between base 10 Vs. base 8
December => DEC
October => OCT
Mathematically, DEC(imal) 25 = OCT(al) 31 or more to put it colloquially:
Christmas = Halloween
Anith

Bitwise operators

Is there such a thing in MS SQL? In MySQL you can use shift operators (<< >>)
and comparison operators such as &.
This is very useful for storing multiple values in a single number. For
example, the number 3 containts two values of power 2.
2 ^ 0 and 2 ^ 1
Because:
2 ^ 0 = 1
and
2 ^ 1 = 2
as a result
1 + 2 = 3
Useing the & operator, we can determine this in a comparison. For example,
SELECT 3 & 1;
this would return 1, indicating that 2^0 is in there. If a zero was
returned, we know that the value is not there.
Anyone have any suggestions?
TSQL has & and |; what would you need bit shifting for?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:CC3F21DA-7333-4E4E-9A10-F4269F249282@.microsoft.com...
> Is there such a thing in MS SQL? In MySQL you can use shift operators (<<
> and comparison operators such as &.
> This is very useful for storing multiple values in a single number. For
> example, the number 3 containts two values of power 2.
> 2 ^ 0 and 2 ^ 1
> Because:
> 2 ^ 0 = 1
> and
> 2 ^ 1 = 2
> as a result
> 1 + 2 = 3
> Useing the & operator, we can determine this in a comparison. For example,
> SELECT 3 & 1;
> this would return 1, indicating that 2^0 is in there. If a zero was
> returned, we know that the value is not there.
> Anyone have any suggestions?
>
|||Just to add to what Adam said, where @.i is an integer, bigint, etc. type:
@.i / 2 = integer shift left one bit
@.i * 2 = integer shift right one bit
Depending on what you're trying to achieve with your bitwise operators,
there may be better ways to do it... Most of the times I've seen bitwise
operators used in SQL, it's to save space by completely denormalizing data
(99.99999% of the time unnecessarily...), which saves a little space but can
make queries and maintenance unnecessarily convoluted.

"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:CC3F21DA-7333-4E4E-9A10-F4269F249282@.microsoft.com...
> Is there such a thing in MS SQL? In MySQL you can use shift operators (<<
> and comparison operators such as &.
> This is very useful for storing multiple values in a single number. For
> example, the number 3 containts two values of power 2.
> 2 ^ 0 and 2 ^ 1
> Because:
> 2 ^ 0 = 1
> and
> 2 ^ 1 = 2
> as a result
> 1 + 2 = 3
> Useing the & operator, we can determine this in a comparison. For example,
> SELECT 3 & 1;
> this would return 1, indicating that 2^0 is in there. If a zero was
> returned, we know that the value is not there.
> Anyone have any suggestions?
>
|||By the way, I wrote a series of blog posts on this subject (which I still
need to write the final two installments of)... in case anyone is
interested:
http://sqljunkies.com/WebLog/amachan...1/25/6897.aspx
http://sqljunkies.com/WebLog/amachan...1/26/6929.aspx
http://sqljunkies.com/WebLog/amachan...1/28/6972.aspx
http://sqljunkies.com/WebLog/amachan...2/04/7345.aspx
http://sqljunkies.com/WebLog/amachan...2/10/7651.aspx
... eventually I'll write the installment on math operators and a bonus
installment that I had in mind on one other topic -- that I can't remember
right now!
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wdI2e.9988$Zz2.1370@.fe12.lga...
> Just to add to what Adam said, where @.i is an integer, bigint, etc. type:
> @.i / 2 = integer shift left one bit
> @.i * 2 = integer shift right one bit
> Depending on what you're trying to achieve with your bitwise operators,
> there may be better ways to do it... Most of the times I've seen bitwise
> operators used in SQL, it's to save space by completely denormalizing data
> (99.99999% of the time unnecessarily...), which saves a little space but
can[vbcol=seagreen]
> make queries and maintenance unnecessarily convoluted.
>
>
> "stooky" <stooky@.discussions.microsoft.com> wrote in message
> news:CC3F21DA-7333-4E4E-9A10-F4269F249282@.microsoft.com...
(<<[vbcol=seagreen]
example,
>
|||"Adam Machanic" wrote:

> TSQL has & and |; what would you need bit shifting for?
>
Well...we are being sent data (we have no choice in the matter) that is
setup as 0s and 1s. Basically, it's marketing data and the data gatherer
doesn't care about querying the data so much as storing it. So, we have the
possibility of 4000+ fields and each field has a value of 0 or 1. Here is an
example of how bad this setup is. There is a section of data, let's call it
"Year of Car You Own". There are a series of fields from 1900-2005. If the
field 2004 is 1, that means you own a 2004 vehicle. If 2004 and 1972 are both
1, that means you have 2 vehicles , one from each year respectively. This
kind of info can be stored in a bitvector to speed things up.
It is also possible to store the 1s and 0s in strings and use substring
functions but the increased data size along going to strings introduces a
slowness factor of about 3.
|||It seems like this is a data integration problem that you should solve when
you load the data. Load it into a normalized schema rather than have
bitmapped columns just because that's how the customer supplied it. Bitmaps
are a lousy way to store data in SQL.
David Portas
SQL Server MVP
"stooky" wrote:

>
> "Adam Machanic" wrote:
>
> Well...we are being sent data (we have no choice in the matter) that is
> setup as 0s and 1s. Basically, it's marketing data and the data gatherer
> doesn't care about querying the data so much as storing it. So, we have the
> possibility of 4000+ fields and each field has a value of 0 or 1. Here is an
> example of how bad this setup is. There is a section of data, let's call it
> "Year of Car You Own". There are a series of fields from 1900-2005. If the
> field 2004 is 1, that means you own a 2004 vehicle. If 2004 and 1972 are both
> 1, that means you have 2 vehicles , one from each year respectively. This
> kind of info can be stored in a bitvector to speed things up.
> It is also possible to store the 1s and 0s in strings and use substring
> functions but the increased data size along going to strings introduces a
> slowness factor of about 3.
>
|||Storing this info denormalized in a single int value can speed up the load
process - although depending on how many records, it might not be a
significant savings. Presumably you'll be doing this a lot less than you'll
be querying the data. However, it will complicate and slow down your
queries, since you have to perform all that processing on the individual
bits. I would recommend that when you load up the data, normalize it.
i.e.,
Person Table
+--+--+
|PersonID | Name |
+--+--+
| 1 | George |
| 2 | Louis |
+--+--+
Car Table
+--+--+
|PersonID | YearOfCar |
+--+--+
| 1 | 1972 |
| 1 | 2004 |
| 2 | 2001 |
+--+--+
(Note, if the samples above come are illegible, copy and paste into NotePad
with a fixed-width font).
In this example, when you load the data, based on the bits that are set, we
put 2 entries in the CarTable for George (he owns a 1972 car and a 2004
car), and we put one entry in for Louis (he owns a 2001 car). Querying for
this info then just requires a simple join [with no bitwise operators],
which SQL is optimized for. The storage space might increase (I say might,
because you don't need to include irrelevant info - notice that we didn't
include years that individuals *don't* own cars for in the table above), but
SQL is optimized to query normalized, properly indexed tables. You can't
create an index on just bits 2, 3, 6 and 13 of an integer column.
Just a thought...
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:8E631902-984F-4175-862C-E59DD2757DFA@.microsoft.com...
>
> "Adam Machanic" wrote:
>
> Well...we are being sent data (we have no choice in the matter) that is
> setup as 0s and 1s. Basically, it's marketing data and the data gatherer
> doesn't care about querying the data so much as storing it. So, we have
> the
> possibility of 4000+ fields and each field has a value of 0 or 1. Here is
> an
> example of how bad this setup is. There is a section of data, let's call
> it
> "Year of Car You Own". There are a series of fields from 1900-2005. If the
> field 2004 is 1, that means you own a 2004 vehicle. If 2004 and 1972 are
> both
> 1, that means you have 2 vehicles , one from each year respectively. This
> kind of info can be stored in a bitvector to speed things up.
> It is also possible to store the 1s and 0s in strings and use substring
> functions but the increased data size along going to strings introduces a
> slowness factor of about 3.
>

Bitwise operators

Is there such a thing in MS SQL? In MySQL you can use shift operators (<< >>)
and comparison operators such as &.
This is very useful for storing multiple values in a single number. For
example, the number 3 containts two values of power 2.
2 ^ 0 and 2 ^ 1
Because:
2 ^ 0 = 1
and
2 ^ 1 = 2
as a result
1 + 2 = 3
Useing the & operator, we can determine this in a comparison. For example,
SELECT 3 & 1;
this would return 1, indicating that 2^0 is in there. If a zero was
returned, we know that the value is not there.
Anyone have any suggestions?TSQL has & and |; what would you need bit shifting for?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:CC3F21DA-7333-4E4E-9A10-F4269F249282@.microsoft.com...
> Is there such a thing in MS SQL? In MySQL you can use shift operators (<<
>>)
> and comparison operators such as &.
> This is very useful for storing multiple values in a single number. For
> example, the number 3 containts two values of power 2.
> 2 ^ 0 and 2 ^ 1
> Because:
> 2 ^ 0 = 1
> and
> 2 ^ 1 = 2
> as a result
> 1 + 2 = 3
> Useing the & operator, we can determine this in a comparison. For example,
> SELECT 3 & 1;
> this would return 1, indicating that 2^0 is in there. If a zero was
> returned, we know that the value is not there.
> Anyone have any suggestions?
>|||Just to add to what Adam said, where @.i is an integer, bigint, etc. type:
@.i / 2 = integer shift left one bit
@.i * 2 = integer shift right one bit
Depending on what you're trying to achieve with your bitwise operators,
there may be better ways to do it... Most of the times I've seen bitwise
operators used in SQL, it's to save space by completely denormalizing data
(99.99999% of the time unnecessarily...), which saves a little space but can
make queries and maintenance unnecessarily convoluted.
:)
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:CC3F21DA-7333-4E4E-9A10-F4269F249282@.microsoft.com...
> Is there such a thing in MS SQL? In MySQL you can use shift operators (<<
> >>)
> and comparison operators such as &.
> This is very useful for storing multiple values in a single number. For
> example, the number 3 containts two values of power 2.
> 2 ^ 0 and 2 ^ 1
> Because:
> 2 ^ 0 = 1
> and
> 2 ^ 1 = 2
> as a result
> 1 + 2 = 3
> Useing the & operator, we can determine this in a comparison. For example,
> SELECT 3 & 1;
> this would return 1, indicating that 2^0 is in there. If a zero was
> returned, we know that the value is not there.
> Anyone have any suggestions?
>|||By the way, I wrote a series of blog posts on this subject (which I still
need to write the final two installments of)... in case anyone is
interested:
http://sqljunkies.com/WebLog/amachanic/archive/2005/01/25/6897.aspx
http://sqljunkies.com/WebLog/amachanic/archive/2005/01/26/6929.aspx
http://sqljunkies.com/WebLog/amachanic/archive/2005/01/28/6972.aspx
http://sqljunkies.com/WebLog/amachanic/archive/2005/02/04/7345.aspx
http://sqljunkies.com/WebLog/amachanic/archive/2005/02/10/7651.aspx
... eventually I'll write the installment on math operators and a bonus
installment that I had in mind on one other topic -- that I can't remember
right now!
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wdI2e.9988$Zz2.1370@.fe12.lga...
> Just to add to what Adam said, where @.i is an integer, bigint, etc. type:
> @.i / 2 = integer shift left one bit
> @.i * 2 = integer shift right one bit
> Depending on what you're trying to achieve with your bitwise operators,
> there may be better ways to do it... Most of the times I've seen bitwise
> operators used in SQL, it's to save space by completely denormalizing data
> (99.99999% of the time unnecessarily...), which saves a little space but
can
> make queries and maintenance unnecessarily convoluted.
> :)
>
> "stooky" <stooky@.discussions.microsoft.com> wrote in message
> news:CC3F21DA-7333-4E4E-9A10-F4269F249282@.microsoft.com...
> > Is there such a thing in MS SQL? In MySQL you can use shift operators
(<<
> > >>)
> > and comparison operators such as &.
> >
> > This is very useful for storing multiple values in a single number. For
> > example, the number 3 containts two values of power 2.
> >
> > 2 ^ 0 and 2 ^ 1
> >
> > Because:
> >
> > 2 ^ 0 = 1
> >
> > and
> >
> > 2 ^ 1 = 2
> >
> > as a result
> >
> > 1 + 2 = 3
> >
> > Useing the & operator, we can determine this in a comparison. For
example,
> >
> > SELECT 3 & 1;
> >
> > this would return 1, indicating that 2^0 is in there. If a zero was
> > returned, we know that the value is not there.
> >
> > Anyone have any suggestions?
> >
> >
>|||"Adam Machanic" wrote:
> TSQL has & and |; what would you need bit shifting for?
>
Well...we are being sent data (we have no choice in the matter) that is
setup as 0s and 1s. Basically, it's marketing data and the data gatherer
doesn't care about querying the data so much as storing it. So, we have the
possibility of 4000+ fields and each field has a value of 0 or 1. Here is an
example of how bad this setup is. There is a section of data, let's call it
"Year of Car You Own". There are a series of fields from 1900-2005. If the
field 2004 is 1, that means you own a 2004 vehicle. If 2004 and 1972 are both
1, that means you have 2 vehicles , one from each year respectively. This
kind of info can be stored in a bitvector to speed things up.
It is also possible to store the 1s and 0s in strings and use substring
functions but the increased data size along going to strings introduces a
slowness factor of about 3.|||It seems like this is a data integration problem that you should solve when
you load the data. Load it into a normalized schema rather than have
bitmapped columns just because that's how the customer supplied it. Bitmaps
are a lousy way to store data in SQL.
--
David Portas
SQL Server MVP
--
"stooky" wrote:
>
> "Adam Machanic" wrote:
> > TSQL has & and |; what would you need bit shifting for?
> >
> Well...we are being sent data (we have no choice in the matter) that is
> setup as 0s and 1s. Basically, it's marketing data and the data gatherer
> doesn't care about querying the data so much as storing it. So, we have the
> possibility of 4000+ fields and each field has a value of 0 or 1. Here is an
> example of how bad this setup is. There is a section of data, let's call it
> "Year of Car You Own". There are a series of fields from 1900-2005. If the
> field 2004 is 1, that means you own a 2004 vehicle. If 2004 and 1972 are both
> 1, that means you have 2 vehicles , one from each year respectively. This
> kind of info can be stored in a bitvector to speed things up.
> It is also possible to store the 1s and 0s in strings and use substring
> functions but the increased data size along going to strings introduces a
> slowness factor of about 3.
>|||Storing this info denormalized in a single int value can speed up the load
process - although depending on how many records, it might not be a
significant savings. Presumably you'll be doing this a lot less than you'll
be querying the data. However, it will complicate and slow down your
queries, since you have to perform all that processing on the individual
bits. I would recommend that when you load up the data, normalize it.
i.e.,
Person Table
+--+--+
|PersonID | Name |
+--+--+
| 1 | George |
| 2 | Louis |
+--+--+
Car Table
+--+--+
|PersonID | YearOfCar |
+--+--+
| 1 | 1972 |
| 1 | 2004 |
| 2 | 2001 |
+--+--+
(Note, if the samples above come are illegible, copy and paste into NotePad
with a fixed-width font).
In this example, when you load the data, based on the bits that are set, we
put 2 entries in the CarTable for George (he owns a 1972 car and a 2004
car), and we put one entry in for Louis (he owns a 2001 car). Querying for
this info then just requires a simple join [with no bitwise operators],
which SQL is optimized for. The storage space might increase (I say might,
because you don't need to include irrelevant info - notice that we didn't
include years that individuals *don't* own cars for in the table above), but
SQL is optimized to query normalized, properly indexed tables. You can't
create an index on just bits 2, 3, 6 and 13 of an integer column.
Just a thought...
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:8E631902-984F-4175-862C-E59DD2757DFA@.microsoft.com...
>
> "Adam Machanic" wrote:
>> TSQL has & and |; what would you need bit shifting for?
> Well...we are being sent data (we have no choice in the matter) that is
> setup as 0s and 1s. Basically, it's marketing data and the data gatherer
> doesn't care about querying the data so much as storing it. So, we have
> the
> possibility of 4000+ fields and each field has a value of 0 or 1. Here is
> an
> example of how bad this setup is. There is a section of data, let's call
> it
> "Year of Car You Own". There are a series of fields from 1900-2005. If the
> field 2004 is 1, that means you own a 2004 vehicle. If 2004 and 1972 are
> both
> 1, that means you have 2 vehicles , one from each year respectively. This
> kind of info can be stored in a bitvector to speed things up.
> It is also possible to store the 1s and 0s in strings and use substring
> functions but the increased data size along going to strings introduces a
> slowness factor of about 3.
>

Bitwise operators

Is there such a thing in MS SQL? In mysql you can use shift operators (<< >>
)
and comparison operators such as &.
This is very useful for storing multiple values in a single number. For
example, the number 3 containts two values of power 2.
2 ^ 0 and 2 ^ 1
Because:
2 ^ 0 = 1
and
2 ^ 1 = 2
as a result
1 + 2 = 3
Useing the & operator, we can determine this in a comparison. For example,
SELECT 3 & 1;
this would return 1, indicating that 2^0 is in there. If a zero was
returned, we know that the value is not there.
Anyone have any suggestions?TSQL has & and |; what would you need bit shifting for?
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:CC3F21DA-7333-4E4E-9A10-F4269F249282@.microsoft.com...
> Is there such a thing in MS SQL? In mysql you can use shift operators (<<
> and comparison operators such as &.
> This is very useful for storing multiple values in a single number. For
> example, the number 3 containts two values of power 2.
> 2 ^ 0 and 2 ^ 1
> Because:
> 2 ^ 0 = 1
> and
> 2 ^ 1 = 2
> as a result
> 1 + 2 = 3
> Useing the & operator, we can determine this in a comparison. For example,
> SELECT 3 & 1;
> this would return 1, indicating that 2^0 is in there. If a zero was
> returned, we know that the value is not there.
> Anyone have any suggestions?
>|||Just to add to what Adam said, where @.i is an integer, bigint, etc. type:
@.i / 2 = integer shift left one bit
@.i * 2 = integer shift right one bit
Depending on what you're trying to achieve with your bitwise operators,
there may be better ways to do it... Most of the times I've seen bitwise
operators used in SQL, it's to save space by completely denormalizing data
(99.99999% of the time unnecessarily...), which saves a little space but can
make queries and maintenance unnecessarily convoluted.

"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:CC3F21DA-7333-4E4E-9A10-F4269F249282@.microsoft.com...
> Is there such a thing in MS SQL? In mysql you can use shift operators (<<
> and comparison operators such as &.
> This is very useful for storing multiple values in a single number. For
> example, the number 3 containts two values of power 2.
> 2 ^ 0 and 2 ^ 1
> Because:
> 2 ^ 0 = 1
> and
> 2 ^ 1 = 2
> as a result
> 1 + 2 = 3
> Useing the & operator, we can determine this in a comparison. For example,
> SELECT 3 & 1;
> this would return 1, indicating that 2^0 is in there. If a zero was
> returned, we know that the value is not there.
> Anyone have any suggestions?
>|||By the way, I wrote a series of blog posts on this subject (which I still
need to write the final two installments of)... in case anyone is
interested:
http://sqljunkies.com/WebLog/amacha...01/25/6897.aspx
http://sqljunkies.com/WebLog/amacha...01/26/6929.aspx
http://sqljunkies.com/WebLog/amacha...01/28/6972.aspx
http://sqljunkies.com/WebLog/amacha...02/04/7345.aspx
http://sqljunkies.com/WebLog/amacha...02/10/7651.aspx
... eventually I'll write the installment on math operators and a bonus
installment that I had in mind on one other topic -- that I can't remember
right now!
Adam Machanic
SQL Server MVP
http://www.datamanipulation.net
--
"Michael C#" <xyz@.abcdef.com> wrote in message
news:wdI2e.9988$Zz2.1370@.fe12.lga...
> Just to add to what Adam said, where @.i is an integer, bigint, etc. type:
> @.i / 2 = integer shift left one bit
> @.i * 2 = integer shift right one bit
> Depending on what you're trying to achieve with your bitwise operators,
> there may be better ways to do it... Most of the times I've seen bitwise
> operators used in SQL, it's to save space by completely denormalizing data
> (99.99999% of the time unnecessarily...), which saves a little space but
can
> make queries and maintenance unnecessarily convoluted.
>
>
> "stooky" <stooky@.discussions.microsoft.com> wrote in message
> news:CC3F21DA-7333-4E4E-9A10-F4269F249282@.microsoft.com...
(<<[vbcol=seagreen]
example,[vbcol=seagreen]
>|||"Adam Machanic" wrote:

> TSQL has & and |; what would you need bit shifting for?
>
Well...we are being sent data (we have no choice in the matter) that is
setup as 0s and 1s. Basically, it's marketing data and the data gatherer
doesn't care about querying the data so much as storing it. So, we have the
possibility of 4000+ fields and each field has a value of 0 or 1. Here is an
example of how bad this setup is. There is a section of data, let's call it
"Year of Car You Own". There are a series of fields from 1900-2005. If the
field 2004 is 1, that means you own a 2004 vehicle. If 2004 and 1972 are bot
h
1, that means you have 2 vehicles , one from each year respectively. This
kind of info can be stored in a bitvector to speed things up.
It is also possible to store the 1s and 0s in strings and use substring
functions but the increased data size along going to strings introduces a
slowness factor of about 3.|||It seems like this is a data integration problem that you should solve when
you load the data. Load it into a normalized schema rather than have
bitmapped columns just because that's how the customer supplied it. Bitmaps
are a lousy way to store data in SQL.
David Portas
SQL Server MVP
--
"stooky" wrote:

>
> "Adam Machanic" wrote:
>
> Well...we are being sent data (we have no choice in the matter) that is
> setup as 0s and 1s. Basically, it's marketing data and the data gatherer
> doesn't care about querying the data so much as storing it. So, we have t
he
> possibility of 4000+ fields and each field has a value of 0 or 1. Here is
an
> example of how bad this setup is. There is a section of data, let's call i
t
> "Year of Car You Own". There are a series of fields from 1900-2005. If the
> field 2004 is 1, that means you own a 2004 vehicle. If 2004 and 1972 are b
oth
> 1, that means you have 2 vehicles , one from each year respectively. This
> kind of info can be stored in a bitvector to speed things up.
> It is also possible to store the 1s and 0s in strings and use substring
> functions but the increased data size along going to strings introduces a
> slowness factor of about 3.
>|||Storing this info denormalized in a single int value can speed up the load
process - although depending on how many records, it might not be a
significant savings. Presumably you'll be doing this a lot less than you'll
be querying the data. However, it will complicate and slow down your
queries, since you have to perform all that processing on the individual
bits. I would recommend that when you load up the data, normalize it.
i.e.,
Person Table
+--+--+
|PersonID | Name |
+--+--+
| 1 | George |
| 2 | Louis |
+--+--+
Car Table
+--+--+
|PersonID | YearOfCar |
+--+--+
| 1 | 1972 |
| 1 | 2004 |
| 2 | 2001 |
+--+--+
(Note, if the samples above come are illegible, copy and paste into NotePad
with a fixed-width font).
In this example, when you load the data, based on the bits that are set, we
put 2 entries in the CarTable for George (he owns a 1972 car and a 2004
car), and we put one entry in for Louis (he owns a 2001 car). Querying for
this info then just requires a simple join [with no bitwise operators],
which SQL is optimized for. The storage space might increase (I say might,
because you don't need to include irrelevant info - notice that we didn't
include years that individuals *don't* own cars for in the table above), but
SQL is optimized to query normalized, properly indexed tables. You can't
create an index on just bits 2, 3, 6 and 13 of an integer column.
Just a thought...
"stooky" <stooky@.discussions.microsoft.com> wrote in message
news:8E631902-984F-4175-862C-E59DD2757DFA@.microsoft.com...
>
> "Adam Machanic" wrote:
>
> Well...we are being sent data (we have no choice in the matter) that is
> setup as 0s and 1s. Basically, it's marketing data and the data gatherer
> doesn't care about querying the data so much as storing it. So, we have
> the
> possibility of 4000+ fields and each field has a value of 0 or 1. Here is
> an
> example of how bad this setup is. There is a section of data, let's call
> it
> "Year of Car You Own". There are a series of fields from 1900-2005. If the
> field 2004 is 1, that means you own a 2004 vehicle. If 2004 and 1972 are
> both
> 1, that means you have 2 vehicles , one from each year respectively. This
> kind of info can be stored in a bitvector to speed things up.
> It is also possible to store the 1s and 0s in strings and use substring
> functions but the increased data size along going to strings introduces a
> slowness factor of about 3.
>