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