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