Bitwise question -

I am new to bitwise thing in MSSQL.

Let's suppose there's a table of favorite foods

insert int fav_foods(food_name,bitwiseVal)
values('Pasta',1)

insert int fav_foods(food_name,bitwiseVal)
values('Chicken',2)

insert int fav_foods(food_name,bitwiseVal)
values('Beef',4)

insert int fav_foods(food_name,bitwiseVal)
values('Fish',8)

insert int fav_foods(food_name,bitwiseVal)
values('Pork',16)

How do I write query to find people who selected more than one item and
selected items from "Pasta, Chicken, Beef, Pork"(but not fish)?
I hope my question is not confusing....Bostonasian wrote:
> I am new to bitwise thing in MSSQL.
> Let's suppose there's a table of favorite foods
> insert int fav_foods(food_name,bitwiseVal)
> values('Pasta',1)
> insert int fav_foods(food_name,bitwiseVal)
> values('Chicken',2)
> insert int fav_foods(food_name,bitwiseVal)
> values('Beef',4)
> insert int fav_foods(food_name,bitwiseVal)
> values('Fish',8)
> insert int fav_foods(food_name,bitwiseVal)
> values('Pork',16)
> How do I write query to find people who selected more than one item and
> selected items from "Pasta, Chicken, Beef, Pork"(but not fish)?
> I hope my question is not confusing....

Your question isn't confusing but your design decision is. Why use
bitwise on something like this? If you were to use proper table design
this query would be trivial (and fast).

Zach|||I tried to simply the example as much as possible, that's probably why
it didn't look that neccesary to build table like this.

I actually have survey data. Survey answer includes text, single select
multiple choice and multi-select multiple choice.

In answered data table, I currently have schema like following :

customer | question_id | answer
---------------
John | 1 | Pasta
John | 1 | Beef
John | 1 | Chicken
John | 1 | Pork

And I've got 2.4 million customers to manage, so I thought it'd save
some rows by using bitwise to reduce row numbers to one.|||Bostonasian wrote:
> I tried to simply the example as much as possible, that's probably why
> it didn't look that neccesary to build table like this.
> I actually have survey data. Survey answer includes text, single select
> multiple choice and multi-select multiple choice.
> In answered data table, I currently have schema like following :
> customer | question_id | answer
> ---------------
> John | 1 | Pasta
> John | 1 | Beef
> John | 1 | Chicken
> John | 1 | Pork
> And I've got 2.4 million customers to manage, so I thought it'd save
> some rows by using bitwise to reduce row numbers to one.

What you save in rows (i.e. disk space, which is cheap), you'll likely
lose in readability, mainainability, performance and standardization.
Search out one of Joe Celko's rants about thinking like a procedural
programmer and not a SQL/set based programmer because I think that's the
problem here.

Zach|||> And I've got 2.4 million customers to manage, so I thought it'd save
> some rows by using bitwise to reduce row numbers to one.

I'll bet that disk space is much cheaper than the cost of the time you'll
spend fixing up a kludge like that :-)

Try this:

CREATE TABLE Foods (customer_id INTEGER NOT NULL REFERENCES Customers
(customer_id), food INTEGER NOT NULL REFERENCES Foods (food), PRIMARY KEY
(customer_id, food))

SELECT customer_id
FROM Foods
WHERE food IN (1,2,3,4,5) /* Pasta,Chicken,Beef,Pork,Fish */
GROUP BY customer_id
HAVING COUNT(CASE WHEN food IN (1,2,3,4) THEN 1 END) = COUNT(*)
/* Everything except fish */

--
David Portas
SQL Server MVP
--|||Bostonasian (axkixx@.gmail.com) writes:
> I am new to bitwise thing in MSSQL.
> Let's suppose there's a table of favorite foods
> insert int fav_foods(food_name,bitwiseVal)
> values('Pasta',1)
> insert int fav_foods(food_name,bitwiseVal)
> values('Chicken',2)
> insert int fav_foods(food_name,bitwiseVal)
> values('Beef',4)
> insert int fav_foods(food_name,bitwiseVal)
> values('Fish',8)
> insert int fav_foods(food_name,bitwiseVal)
> values('Pork',16)
> How do I write query to find people who selected more than one item and
> selected items from "Pasta, Chicken, Beef, Pork"(but not fish)?
> I hope my question is not confusing....

SELECT *
FROM tbl
WHERE fav_food & (SELECT SUM(bitwiseVal)
FROM fav_foods
WHERE food_name IN ('Pasta', 'Chicken', 'Beef', 'Pork'))

But as pointed out by others, this is a poor design. You may
save disk space, but if you need to find all that selected Chicken,
you will find that you cannot have an index on bit in an integer
column, so you get awful performance.

Look at David's query, and use that instead of the above.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Bostonasian wrote:
> I tried to simply the example as much as possible, that's probably why
> it didn't look that neccesary to build table like this.
> I actually have survey data. Survey answer includes text, single select
> multiple choice and multi-select multiple choice.
> In answered data table, I currently have schema like following :
> customer | question_id | answer
> ---------------
> John | 1 | Pasta
> John | 1 | Beef
> John | 1 | Chicken
> John | 1 | Pork
> And I've got 2.4 million customers to manage, so I thought it'd save
> some rows by using bitwise to reduce row numbers to one.

Hi Bostonasian,

There is no need to denormalize or use bitwise operations for this. IMO,
bitwise operations are not suitable for this problem.

The database does not have to grow very fast. If you normalize all the
way through, you would get a Customers (reference) table, a Questions
(reference) table and a Answers (reference) table. All these reference
tables can have short keys, which you use in your CustomerAnswers (data)
table. If you have fewer than 64000 customers, fewer than 256 questions
and fewer than 256 (fixed) answers per question, then each row in
CustomerAnswers would be just 2+1+1 = 4 bytes (excluding the free format
text answers).

Your schema could look something like this:
CREATE TABLE Customers(CustomerID smallint PRIMARY KEY, Name
nvarchar(100))
CREATE TABLE Questions(QuestionID tinyint PRIMARY KEY, Question
nvarchar(3000))
CREATE TABLE Answers (QuestionID tinyint, AnswerID tinyint, Answer
nvarchar(200),PRIMARY KEY (QuestionID,AnswerID))

CREATE TABLE CustomerAnswers
(CustomerID smallint REFERENCES Customers
,QuestionID tinyint REFERENCES Questions
,AnswerID tinyint
,TextAnswer nvarchar(2000)
,PRIMARY KEY (CustomerID,QuestionID,AnswerID)
,FOREIGN KEY (QuestionID,AnswerID) REFERENCES Answers
)

Hope this helps,
Gert-Jan

Bitwise or aggregate function

Hi,
I understand there are no aggregate function for ORing multiple row columns
together. We have a access control system that looks like
CREATE TABLE AccessTable
(
UserID int NOT NULL PRIMARY KEY,
EntityID int NOT NULL PRIMARY KEY,
AccessMask int NOT NULL
)
where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
etc. We would like to be able to do something like
select AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = @.EntityID
I've seen some samples of a two table solution but I just wanted to double
check if someone has a better solution. Running SQL Server 2005.
Thanks,
MansoManso wrote:
> Hi,
> I understand there are no aggregate function for ORing multiple row column
s
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
> Thanks,
> Manso
Why would you store this data as a bitmap in the first place? Try:
SELECT
MAX(AccessMask & 1)+
MAX(AccessMask & 2)+
MAX(AccessMask & 4)+
MAX(AccessMask & 8)+
MAX(AccessMask & 16)+
MAX(AccessMask & 32)+
MAX(AccessMask & 64)+
MAX(AccessMask & 128) AS aggr_orsum
FROM AccessTable
WHERE EntityID = @.EntityID ;
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"Manso" <Manso@.discussions.microsoft.com> wrote in message
news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Hi,
> I understand there are no aggregate function for ORing multiple row
> columns
> together. We have a access control system that looks like
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL PRIMARY KEY,
> EntityID int NOT NULL PRIMARY KEY,
> AccessMask int NOT NULL
> )
> where AccessMask contains an int with values ORed together 1, 2, 4, 8, 16
> etc. We would like to be able to do something like
> select AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = @.EntityID
> I've seen some samples of a two table solution but I just wanted to double
> check if someone has a better solution. Running SQL Server 2005.
>
Well, it so happens SQL Server 2005 supports User-Defined Aggregate
functions.
For instance
--Aggregate.cs--
using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using Microsoft.SqlServer.Server;
[Serializable]
[SqlUserDefinedAggregate(Format.Native,
IsInvariantToDuplicates=true,
IsInvariantToNulls = true,
IsInvariantToOrder = true,
IsNullIfEmpty = false)]
public struct AGGR_ORSUM
{
int accumulator;
public void Init()
{
accumulator = 0;
}
public void Accumulate(SqlInt32 Value)
{
if (Value.IsNull)
return;
accumulator |= Value.Value;
}
public void Merge(AGGR_ORSUM Group)
{
accumulator |= Group.accumulator;
}
public SqlInt32 Terminate()
{
return new SqlInt32(accumulator);
}
}
--end Aggregate.cs--
--Test.sql--
\
drop table AccessTable
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL ,
AccessMask int NOT NULL
)
insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
select dbo.AGGR_ORSUM(AccessMask) As AggSum
from AccessTable
where EntityID = 1
--end Test.sql--
David|||Thanks for your prompt reply David.
Because it's a convenient way of representing access masks. I know it has a
few drawbacks but the upside is we can append new access bits without adding
columns or modifying the schema. Since we're never using the access column i
n
a where clause I find it to be the best option. We have about 13 different
access types today but that will increase.
I just managed to put together a function that does what we want:
declare @.Return int
set @.Return = 0
select @.Return = @.Return | AccessMask
from AccessTable
where where EntityID = @.EntityID
return @.Return
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> Why would you store this data as a bitmap in the first place? Try:
> SELECT
> MAX(AccessMask & 1)+
> MAX(AccessMask & 2)+
> MAX(AccessMask & 4)+
> MAX(AccessMask & 8)+
> MAX(AccessMask & 16)+
> MAX(AccessMask & 32)+
> MAX(AccessMask & 64)+
> MAX(AccessMask & 128) AS aggr_orsum
> FROM AccessTable
> WHERE EntityID = @.EntityID ;
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Manso wrote:
> Thanks for your prompt reply David.
> Because it's a convenient way of representing access masks. I know it has
a
> few drawbacks but the upside is we can append new access bits without addi
ng
> columns or modifying the schema. Since we're never using the access column
in
> a where clause I find it to be the best option. We have about 13 different
> access types today but that will increase.
How about :
CREATE TABLE AccessTable
(
UserID int NOT NULL,
EntityID int NOT NULL,
AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
PRIMARY KEY (UserID, EntityID, AccessTypeID)
)

> I just managed to put together a function that does what we want:
> declare @.Return int
> set @.Return = 0
> select @.Return = @.Return | AccessMask
> from AccessTable
> where where EntityID = @.EntityID
> return @.Return
>
That gives an undefined result because it's a multi-row assignment,
which isn't officially supported. For that reason it may break under
some conditions. Also, it isn't a general aggregation solution because
it cannot GROUP.
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||Beautiful, David. Thanks a lot. We've been really indecisive whether to use
CLR in this project but maybe we should look into it...looks .
Thanks,
Manso
"David Browne" wrote:

> "Manso" <Manso@.discussions.microsoft.com> wrote in message
> news:C61C4BDC-BFAD-4B20-B379-3504674E28A4@.microsoft.com...
> Well, it so happens SQL Server 2005 supports User-Defined Aggregate
> functions.
> For instance
> --Aggregate.cs--
> using System;
> using System.Data;
> using System.Data.SqlClient;
> using System.Data.SqlTypes;
> using Microsoft.SqlServer.Server;
>
> [Serializable]
> [SqlUserDefinedAggregate(Format.Native,
> IsInvariantToDuplicates=true,
> IsInvariantToNulls = true,
> IsInvariantToOrder = true,
> IsNullIfEmpty = false)]
> public struct AGGR_ORSUM
> {
> int accumulator;
> public void Init()
> {
> accumulator = 0;
> }
> public void Accumulate(SqlInt32 Value)
> {
> if (Value.IsNull)
> return;
> accumulator |= Value.Value;
> }
> public void Merge(AGGR_ORSUM Group)
> {
> accumulator |= Group.accumulator;
> }
> public SqlInt32 Terminate()
> {
> return new SqlInt32(accumulator);
> }
>
> }
> --end Aggregate.cs--
> --Test.sql--
> \
> drop table AccessTable
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL ,
> AccessMask int NOT NULL
> )
> insert into AccessTable(UserID, EntityID,AccessMask) values (1,1,2)
> insert into AccessTable(UserID, EntityID,AccessMask) values (2,1,16)
> insert into AccessTable(UserID, EntityID,AccessMask) values (3,1,2)
> select dbo.AGGR_ORSUM(AccessMask) As AggSum
> from AccessTable
> where EntityID = 1
> --end Test.sql--
> David
>
>|||I left out the check constraint for clarity.
I read somewhere else that this could give unwanted results somewhere else
but I haven't managed to find out under which circumstances. Do you know
when/why/how this could break?
Thanks,
Manso
"David Portas" wrote:

> Manso wrote:
> How about :
> CREATE TABLE AccessTable
> (
> UserID int NOT NULL,
> EntityID int NOT NULL,
> AccessTypeID int NOT NULL CHECK AccessType BETWEEN (1 AND 13),
> PRIMARY KEY (UserID, EntityID, AccessTypeID)
> )
>
> That gives an undefined result because it's a multi-row assignment,
> which isn't officially supported. For that reason it may break under
> some conditions. Also, it isn't a general aggregation solution because
> it cannot GROUP.
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Manso wrote:
> I left out the check constraint for clarity.
But in my example you don't need the bitmap (because I changed the
key).

> I read somewhere else that this could give unwanted results somewhere else
> but I haven't managed to find out under which circumstances. Do you know
> when/why/how this could break?
Books Online (2000 and 2005) says
"If the SELECT statement returns more than one value, the variable is
assigned the last value returned."
So if your "aggregate" solution works it is arguably a bug!
The following KB confirms that "The correct behavior for an aggregate
concatenation query is undefined." So you are definitely on thin ice if
you rely on it.
http://support.microsoft.com/kb/287515/en-us
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
--|||"The correct behavior for an aggregate concatenation query is undefined."
This classic contrived phrase was written by some still unknown fuzzball
confirmed by
one intelligent MS employee.It is meaningless and that's putting it kindly:)
Perhaps it was Celko:) My guess is that since it came from Sybase MS thinks
the less said
about it the better.Aside from the functionality,which boils down to 'it
depends',
it is another example that 'words due matter':)
For interested readers see what sybase says about it (especially in Update)
in their
enterprise db.
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1145543785.636307.71580@.i39g2000cwa.googlegroups.com...
> Manso wrote:
> But in my example you don't need the bitmap (because I changed the
> key).
>
> Books Online (2000 and 2005) says
> "If the SELECT statement returns more than one value, the variable is
> assigned the last value returned."
> So if your "aggregate" solution works it is arguably a bug!
> The following KB confirms that "The correct behavior for an aggregate
> concatenation query is undefined." So you are definitely on thin ice if
> you rely on it.
> http://support.microsoft.com/kb/287515/en-us
> --
> David Portas, SQL Server MVP
> Whenever possible please post enough code to reproduce your problem.
> Including CREATE TABLE and INSERT statements usually helps.
> State what version of SQL Server you are using and specify the content
> of any error messages.
> SQL Server Books Online:
> http://msdn2.microsoft.com/library/ms130214(en-US,SQL.90).aspx
> --
>|||Writing assembly language in SQL will come back and get you. Use a
normalized table design with one column for each access privilege or a
hierarchy of privileges in a nested sets model.
Also, you had two PRIMARY KEYs!

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.
>

Bitwise operator question

What is the biggest values that SQL Server supports for bitwise AND and OR.
The BOL says any of the integer datatypes. But in the examples, it only r
eferences int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some confirmat
ion from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBASure:
SELECT CONVERT(BIGINT, 35184372088833) & 1
Note: You do need to explicitly convert it to BIGINT or else SQL Server wil
l try to implicitly convert it to NUMERIC, which does not support bitwise co
mparisons.
--
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rick Sawtell" <quickening@.msn.com> wrote in message news:eBlLVTM%23EHA.1396
@.tk2msftngp13.phx.gbl...
What is the biggest values that SQL Server supports for bitwise AND and OR.
The BOL says any of the integer datatypes. But in the examples, it only r
eferences int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some confirmat
ion from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBA|||Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to hold
data in a strongly-typed database though, so you shouldn't need bitwise
operators very often.
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3772EF4-A53D-4740-9835-642A6BBF767F@.microsoft.com...
> Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to
> hold
> data in a strongly-typed database though, so you shouldn't need bitwise
> operators very often.
> --
> David Portas
> SQL Server MVP
> --
>
How would you suggest tracking 16000 different options.
Rick|||"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23svAjAO%23EHA.1524@.TK2MSFTNGP09.phx.gbl...
> How would you suggest tracking 16000 different options.
Certainly not in a 16000-bit bitmask... Assuming those options were for
users, I would do something like this:
CREATE TABLE Options
(
OptionID INT PRIMARY KEY,
OptionName VARCHAR(50),
..
)
CREATE TABLE UserOptions
(
OptionID INT,
UserID INT,
CONSTRAINT FK_UserOptions_Options FOREIGN KEY (OptionID) REFERENCES
Options (OptionID),
CONSTRAINT FK_UserOptions_Users FOREIGN KEY (UserID) REFERENCES
Users (UserID),
CONSTRAINT PK_UserOptions PRIMARY KEY (OptionID, UserID)
)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||In a table, with atomic columns representing real attributes. How else? :-)
If you have that many elements to track then I would say bitmaps are
definitely no-go. Bitmaps don't benefit from optimization on individual
attributes and every query will force a table-scan with the bitwise
operations performed on every row.
David Portas
SQL Server MVP
--

Bitwise operator question

This is a multi-part message in MIME format.
--=_NextPart_000_002D_01C4F892.984E52C0
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
What is the biggest values that SQL Server supports for bitwise AND and = OR.
The BOL says any of the integer datatypes. But in the examples, it = only references int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some = confirmation from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBA
--=_NextPart_000_002D_01C4F892.984E52C0
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&
What is the biggest values that SQL = Server supports for bitwise AND and OR.

The BOL says any of the integer datatypes. But in the examples, it only references = int, smallint and tinyint (and binary conversion stuff).

Does SQL support bigint values as = well? eg. 64-bit comparisons?


I've run a couple of sample scripts to = test, but I would like some confirmation from others.


Thanks


Rick Sawtell
MCT, MCSD, MCDBA


--=_NextPart_000_002D_01C4F892.984E52C0--This is a multi-part message in MIME format.
--=_NextPart_000_00C3_01C4F89B.FAB67F60
Content-Type: text/plain;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
Sure:
SELECT CONVERT(BIGINT, 35184372088833) & 1
Note: You do need to explicitly convert it to BIGINT or else SQL Server =will try to implicitly convert it to NUMERIC, which does not support =bitwise comparisons.
-- Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Rick Sawtell" <quickening@.msn.com> wrote in message =news:eBlLVTM%23EHA.1396@.tk2msftngp13.phx.gbl...
What is the biggest values that SQL Server supports for bitwise AND =and OR.
The BOL says any of the integer datatypes. But in the examples, it =only references int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some =confirmation from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBA
--=_NextPart_000_00C3_01C4F89B.FAB67F60
Content-Type: text/html;
charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
&

---

Sure:
SELECT CONVERT(BIGINT, 35184372088833) =& 1
Note: You do need to explicitly =convert it to BIGINT or else SQL Server will try to implicitly convert it to NUMERIC, =which does not support bitwise comparisons.
-- Adam MachanicSQL Server MVPhttp://www.sqljunkies.com/weblog/amachanic">http://www.sqljunkies=.com/weblog/amachanic--
"Rick Sawtell" wrote in =message news:eBlLVTM%23EHA.=1396@.tk2msftngp13.phx.gbl...
What is the biggest values that SQL =Server supports for bitwise AND and OR.

The BOL says any of the integer datatypes. But in the examples, it only references =int, smallint and tinyint (and binary conversion stuff).

Does SQL support bigint values as well? eg. 64-bit comparisons?


I've run a couple of sample scripts =to test, but I would like some confirmation from others.


Thanks


Rick Sawtell
MCT, MCSD, MCDBA

---

--=_NextPart_000_00C3_01C4F89B.FAB67F60--|||Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to hold
data in a strongly-typed database though, so you shouldn't need bitwise
operators very often.
--
David Portas
SQL Server MVP
--|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3772EF4-A53D-4740-9835-642A6BBF767F@.microsoft.com...
> Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to
> hold
> data in a strongly-typed database though, so you shouldn't need bitwise
> operators very often.
> --
> David Portas
> SQL Server MVP
> --
>
How would you suggest tracking 16000 different options.
Rick|||"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23svAjAO%23EHA.1524@.TK2MSFTNGP09.phx.gbl...
> How would you suggest tracking 16000 different options.
Certainly not in a 16000-bit bitmask... Assuming those options were for
users, I would do something like this:
CREATE TABLE Options
(
OptionID INT PRIMARY KEY,
OptionName VARCHAR(50),
...
)
CREATE TABLE UserOptions
(
OptionID INT,
UserID INT,
CONSTRAINT FK_UserOptions_Options FOREIGN KEY (OptionID) REFERENCES
Options (OptionID),
CONSTRAINT FK_UserOptions_Users FOREIGN KEY (UserID) REFERENCES
Users (UserID),
CONSTRAINT PK_UserOptions PRIMARY KEY (OptionID, UserID)
)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--|||In a table, with atomic columns representing real attributes. How else? :-)
If you have that many elements to track then I would say bitmaps are
definitely no-go. Bitmaps don't benefit from optimization on individual
attributes and every query will force a table-scan with the bitwise
operations performed on every row.
--
David Portas
SQL Server MVP
--

Bitwise operator question

What is the biggest values that SQL Server supports for bitwise AND and OR.
The BOL says any of the integer datatypes. But in the examples, it only references int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some confirmation from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBA
Sure:
SELECT CONVERT(BIGINT, 35184372088833) & 1
Note: You do need to explicitly convert it to BIGINT or else SQL Server will try to implicitly convert it to NUMERIC, which does not support bitwise comparisons.
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
"Rick Sawtell" <quickening@.msn.com> wrote in message news:eBlLVTM%23EHA.1396@.tk2msftngp13.phx.gbl...
What is the biggest values that SQL Server supports for bitwise AND and OR.
The BOL says any of the integer datatypes. But in the examples, it only references int, smallint and tinyint (and binary conversion stuff).
Does SQL support bigint values as well? eg. 64-bit comparisons?
I've run a couple of sample scripts to test, but I would like some confirmation from others.
Thanks
Rick Sawtell
MCT, MCSD, MCDBA
|||Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to hold
data in a strongly-typed database though, so you shouldn't need bitwise
operators very often.
David Portas
SQL Server MVP
|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:F3772EF4-A53D-4740-9835-642A6BBF767F@.microsoft.com...
> Bitwise operaters are supported with BIGINT. Bitmaps are a lousy way to
> hold
> data in a strongly-typed database though, so you shouldn't need bitwise
> operators very often.
> --
> David Portas
> SQL Server MVP
> --
>
How would you suggest tracking 16000 different options.
Rick
|||"Rick Sawtell" <quickening@.msn.com> wrote in message
news:%23svAjAO%23EHA.1524@.TK2MSFTNGP09.phx.gbl...
> How would you suggest tracking 16000 different options.
Certainly not in a 16000-bit bitmask... Assuming those options were for
users, I would do something like this:
CREATE TABLE Options
(
OptionID INT PRIMARY KEY,
OptionName VARCHAR(50),
...
)
CREATE TABLE UserOptions
(
OptionID INT,
UserID INT,
CONSTRAINT FK_UserOptions_Options FOREIGN KEY (OptionID) REFERENCES
Options (OptionID),
CONSTRAINT FK_UserOptions_Users FOREIGN KEY (UserID) REFERENCES
Users (UserID),
CONSTRAINT PK_UserOptions PRIMARY KEY (OptionID, UserID)
)
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
|||In a table, with atomic columns representing real attributes. How else? :-)
If you have that many elements to track then I would say bitmaps are
definitely no-go. Bitmaps don't benefit from optimization on individual
attributes and every query will force a table-scan with the bitwise
operations performed on every row.
David Portas
SQL Server MVP

Bitwise operation in SELECT killing performance, *sometimes*?

Hi there,

We have a high volume SQL Server which gets into trouble at times, with one query blocking out others for 10-15 minutes.

The query looks something like this:

SELECT * FROM MyTable WHERE MyQueryColumn & 128 = 128 ORDER BY MySortColumn

There is an index that exists containing MyQueryColumn. MyTable has somewhere around 550,000 rows, around 50 of which might match this query at any particular time.

Almost every time this query executes in under 1 second, and everybody is happy. However, once every few weeks (or months, even), this thing takes a dogs age, blocking loads of other queries trying to hit MyTable.

We did a showplan on the query, and saw something like this

WHERE (Convert([Mydatabase].[MyQueryColumn]) & 128 = 128)

The Convert is making me a little bit nervous; but it doesn't appear to be doing a table scan or anything heinous, although an index scan with that many rows might be bad enough.

Changing the query to something like this is an option:

SELECT * FROM MyTable WHERE MyQueryColumn > 127 ORDER BY MySortColumn

but I'd like someone to tell me that using that bitwise operator might cause such a problem before I modify our code.

Thanks,

Chris
Hi,

I'm not sure how to explain the slowdown without more info.

However, given what you've told us, I don't think the index helps you with that query. The index isn't used for bitwise operators on the indexed column.

You might consider something like

select * from MyTable where (MyQueryColumn > 127) and ((MyQueryColumn & 128) = 128)

or perhaps an indexed computed column.|||An indexed computed column would work, but you would be shifting the slowdown elsewhere, particularly inserts updates and deletes.

so what is the problem?

well. . .

Bitmasking is a violation of normalization!!!

Now that being said. . .

I am willing to bet each bit of the mask corresponds to a particular quality right? the set bit, determines a ID particular quality of some row in your database:

What you are really modeling is a Many to Many relationship -
Items >--< Quality
ItemQuality

Item (ID, ItemName)
Quality(ID, QualityName)
ItemQuality(ItemID, QualityID)
To get back your original table, given that QualityID matches your original bit scheme:
Select ID, ItemName, sum(QualityID) from
Item inner join ItemQuality on Item.Id = ItemQuality.ItemId

but in practice you wouldn't really do that. . . because in the realtional world, you dont care about the ID's/Bitmasks, you care about the realtions so typically you would do

Select ID, ItemName from
(Item inner join ItemQuality
on Item.Id = ItemQuality.ItemId)
inner join Quality
on ItemQuality.QualityID = Quality.ID
where Quality.Name in
('Some Quality', 'Some Other Quality')

Therefore, the "where clause" is in essence your bitmask!

I mean, imagine an application that has a set of check boxes for all the qualities. . .

You decide you want to mask a new quality. If using a bit mask, you have to rewrite your application. Using a normalized schema, the application could be dynamic:

<pseudocode>
For each Quality
GenerateCheckBox
</pseudocode>

so bag the bitmask!!!
By using a bitmask, you are bypassing the entire purpose of an RDBMS!!!|||If you can capture the query plan for the good/bad cases, this would be helpful

set statistics profile on
<<query in question>>
set statistics profile off

Note that this adds overhead, so please do this with care.

In general, an index won't be generally useful in this case (except in the case when you have a very wide row) - the sort isn't helping you because you are just pulling some bit out of the column.

Keeping statistics up-to-date will likely also help avoid/minimize cases where the optimizer gets confused and provides a less-than-optimal query plan.

Conor Cunningham
SQL Server Query Optimization Development Lead

Bitwise NOT Operation

I was looking into some of the System stored procedures like sp_denylogin, sp_grantLogin etc. I found bit wise operations like

update master.dbo.sysxlogins set xstatus = (xstatus & ~1) | 2, xdate2 = getdate()
where name = @.loginame and srvid IS NULL

How does the bitwise NOT(~) works with different datatypes like INT, SMALLINT and TINYINT?

Look at the following sample code and result. I just wanted to understand how it works with int and smallint

declare @.a int
declare @.b smallint
declare @.c tinyint

select @.a = 1, @.b = 1, @.c = 1

select @.a=~@.a, @.b= ~@.b, @.c= ~@.c

select @.a, @.b, @.c

Result
~~~~~
-2 -2 254

Thanks in advance
GnanaInteresting question! In a former life, I worked on addressable converter interfaces, and much was done with bitmaps...so I played a bit also...

Note the results if you execute the following select in your example:
select @.a, @.b, @.c, cast(@.a AS varbinary), cast(@.b as varbinary), cast (@.c as varbinary)

Result
~~~~~
-2 -2 254 0xFFFFFFFE 0xFFFE 0xFE

Now you get to see the binary results...which still does not answer your question, but I betcha (as I head back to BOL) that the answer lies in there as the way the sign bit is interpreted in the larger two data types.

Betcha that the tinyint data type does not have a sign bit.

yep...that's it...from BOL:tinyint

Integer data from 0 through 255. Storage size is 1 byte.

So the issue is not so much that the bitwise operation actually WORKS any differently...what you are seeing is just the PRESENTATION differences between data types (the final select assumes you want the data interpreted in it's numeric value, not the bit-representation of the data in the variable...so you get a negative number because you have set the sign bit on with the bitwise operation you used on the larger data types (that have sign bits).

In other words, the bitwise operator works exactly the same on all three data types, it just LOOKS like it works differently when you select it back to see the results (without casting, that is).|||Nice explanation, Paul.|||Yeah, thanks! I learned something too (something I usually try to avoid at all costs)...though we don't use much bit manipulation here (at least not out where everyone can see ;) ).

bitwise AND with Char column

based on BOL, bitwise AND operator operates only on integer data type
category (int, smallint, tinyint). but i have a char(4) column which
stores integers (disregard the db design issue) which produces the
same/correct results after using bitwise AND with another integer.
does this mean SQL server does the conversion itself?SQL Server will convert a value stored in the CHAR datatype into an INTEGER
because an INTEGER has a high
Data Type Precedence
"joeycalisay" <joeycalisay@.gmail.com> wrote in message
news:1131435508.269803.57870@.o13g2000cwo.googlegroups.com...
> based on BOL, bitwise AND operator operates only on integer data type
> category (int, smallint, tinyint). but i have a char(4) column which
> stores integers (disregard the db design issue) which produces the
> same/correct results after using bitwise AND with another integer.
> does this mean SQL server does the conversion itself?
>|||thanks a lot sir!|||Implicit conversion is at work here and that's potentially bad news.
Implicit conversions are to be avoided where possible because of their
potential to break unexpectedly under schema or code changes or even as
a result of service packs or version upgrades. Obviously a poor design
is at fault in your example but if you must accommodate it then I would
use CASE expressions:
CASE WHEN col1 = '1' AND col2 = '1' THEN 1 ELSE 0 END
David Portas
SQL Server MVP
--|||thanks for the input sir david particularly on the point of it being
breakable. poor design - yeah, i hated it when i saw it, wish i had
the time to revise it but they are at code freeze

Bitwise AND in Where clause?

Is there any way to use Bitwise AND in a WHERE clause?
EG:
SELECT * FROM Directory WHERE DirectoryFeatured & 4
This just gives me an incorrect syntax error? DirectoryFeatured is an int
field.
Regards,
ChrisNot sure how you store the data, but possibly this is what you are looking f
or:
SELECT * FROM Directory WHERE DirectoryFeatured & 4 = 4
Consider normalizing the table and split each attribute to one column (or an
other table, depending
on the data etc).
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message news:42e64dc4.0@.entanet
..
> Is there any way to use Bitwise AND in a WHERE clause?
> EG:
> SELECT * FROM Directory WHERE DirectoryFeatured & 4
> This just gives me an incorrect syntax error? DirectoryFeatured is an int
field.
> Regards,
> Chris
>|||In SQL, WHERE clauses return one of TRUE, FALSE or UNKNOWN.
An t-SQL bitwise expression DirectoryFeatured & 4 returns an INT value which
is incompatible with the above set of values. A logical/bitwise AND between
the two expressions, taking each corresponding bit for both expressions.
Thus your expression should take the form of:
SELECT * FROM tbl WHERE col & 4 = <some val>
In general, there is not many instances when one needs to use bitwise
operators in commerical SQL applications. Though it occasionally finds its
place, quite often poorly designed schema is a prime reason for people
resorting to complex bitwise logic on integer typed columns.
Anith|||The statement is not complete. Try (untested):
SELECT * FROM Directory WHERE DirectoryFeatured & 4 = 4
The bitwise operator will return a value (not True or False); you have to
test for the expected value.
As an aside, this is a relatively inefficient thing to do, and it would
probably be best to create an encoding scheme that did not rely on low-level
bit manipulation.
"Chris Ashley" <chris.ashley@.SPAMblueyonder.co.uk> wrote in message
news:42e64dc4.0@.entanet...
> Is there any way to use Bitwise AND in a WHERE clause?
> EG:
> SELECT * FROM Directory WHERE DirectoryFeatured & 4
> This just gives me an incorrect syntax error? DirectoryFeatured is an int
> field.
> Regards,
> Chris
>

Bitwise & operator does not work

I would like to do something like a SELECT * FROM Files WHERE (Attributes & ?) but the & operator isn't recognized. I looked at the documentation sample for it and in the sample the & operator isn't used in the WHERE clause so I thought that might be the reason but I still can't get the operator to work. So even the doc sample code for it doesn't work.

The code in the documentation sample for & does work for me.

I get the expected result, 10, printed on my console.

Robert Wishlaw

|||I got this to work by rewriting the WHERE clause as (Attributes & ? = ?). However, the VS query designer can't parse the bitwise operators.

Bitwise

I don't know what's bitwise....
How can I use in MSSQL , PHP ?
If you know please teach me...

ThanksThere is a great resource for all kind of learnings. It is called Wikipedia.
Take a look at the Bitwise Operation article there.

SQL Server syntax:

"|" - Bitwise OR
"&" - Bitwise AND
"^" - Bitwise Exclusive OR