March 28, 2011 at 10:40 pm
Comments posted to this topic are about the item The BIT data type
Adam Sottosanti
March 28, 2011 at 11:29 pm
I went went the logic commonly used in boolean systems, where everything that isn't 0 equals to 1. Luckily I was correct. 🙂
Thanks for the question.
Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP
March 29, 2011 at 1:20 am
Simple and easy question.
-----------------
Gobikannan
March 29, 2011 at 1:57 am
Thanks for the question.
I did wonder if going beyond the bound of the int datatype would cause it to fall over, but no. You have to go far beyond that:
DECLARE @bit BIT
SET @bit = 100000000000000000000000000000000000000
SELECT @bit
i.e. past the 10^38 numeric limit.
If you were to write it as:
DECLARE @bit BIT
SET @bit = 1E309
SELECT @bit
it would interpret the number as floating point and allow up to 1E308. So it looks like anything that can be read as a valid numeric type will be interpreted as a bit value of 1.
March 29, 2011 at 4:58 am
Good question and learnt something new today.
March 29, 2011 at 6:45 am
Nice easy one thanks for the point
March 29, 2011 at 6:59 am
I took a different look at it.
Assumed a 2147483647 in binary is
1111111111111111111111111111111
Then stuck it into a bit which is only 1 binary digit wide.
Figured the rest would flow over the register and I'd be left with the least most significant digit or binary 1. 🙂
Learned something knew and got the question right for thw wrong reason.
March 29, 2011 at 7:00 am
If the question had been about
2147483646
That would have been
1111111111111111111111111111110 in binary with a 0 in the least significant position (as all even number are) and while it would have had the same answer... I'd have gotten it wrong. 🙂
March 29, 2011 at 7:36 am
Good question 🙂
M&M
March 29, 2011 at 12:09 pm
Good question. I got it wrong, so I learnt something.
And of course spotted another apparent contradiction in Bol: according to the Bol page for the bit type[/URL] bit is an integer datatype; this means that trying to store an integer other than 0 or 1 in it isn't doing conversion from a non-integer type to an integer type, so should result in overflow. And overflow, according to this Bol page always delivers null.
I wonder what happens with
DECLARE @bit BIT
SET @bit i
Set @BIT += 2147483646
SELECT @bit
Is it NULL, or is it 1? Given the answer to today's QOTD, NULL would seem bizarre; but perhaps not quite so bizarre as 1 would seem.
Tom
March 29, 2011 at 4:35 pm
Thanks for the interesting question!
March 29, 2011 at 11:47 pm
Nice question - thanks
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 30, 2011 at 3:27 am
I tried running the code and an error message popped up saying
"Your approach to SQL is wrong. BIT flags are things we left behind in assembly language. " 😉
March 30, 2011 at 12:50 pm
Tom.Thomson (3/29/2011)
I wonder what happens with
DECLARE @bit BITSET @bit i
Set @BIT += 2147483646
SELECT @bit
Is it NULL, or is it 1?
I assume that you meant the first SET statement to read "SET @bit = 1".
The answer is that this should result in 1. The "SET @bit += 2147483646" is shorthand for "SET @bit= @bit + 2147483646". The constant 2147483646 implies the integer datatype. This data type has a higher precedence than bit, so @bit is cast to integer (value: 1). Then integer arithmetic is used to calculate 1 + 2147483646 (resulting in 2147483647). And finally, this value 2147483647 is converted back to bit for the assignment, resulting in the bit value 1.
March 30, 2011 at 2:12 pm
Hugo Kornelis (3/30/2011)
I assume that you meant the first SET statement to read "SET @bit = 1".
Yes :blush:
The answer is that this should result in 1. The "SET @bit += 2147483646" is shorthand for "SET @bit= @bit + 2147483646". The constant 2147483646 implies the integer datatype. This data type has a higher precedence than bit, so @bit is cast to integer (value: 1). Then integer arithmetic is used to calculate 1 + 2147483646 (resulting in 2147483647). And finally, this value 2147483647 is converted back to bit for the assignment, resulting in the bit value 1.
Sounds reasonable.
It seems pretty clear that in this respect bit is (contrary to the statement in BoL) not an integer data type, since a statement like "select @i = 17179869184" results in arithmentic overflow if the type of @i is any of tinyint, smallint, or int but not of @i is of type bit. Also, this behaviour of the bit type clearly discards both associativity and commutativity of addition for integers; what should one expect the results of
declare @a bit = 1, @b-2 bit = 1,@c bit = 1,@d bit = 1, @i int = 1
select @i = @a+@b+@i+@c+@d; select @i
select @i = 1
select @i = @i+@a+@b+@c+@d; select @i
select @i = 1
select @i = @a+@b+@c+@d+@i; select @i
to be? Clearly it depends on the order in which the additions are carried out, and when conversion(s) from bit to int occur.
Tom
Viewing 15 posts - 1 through 15 (of 18 total)
You must be logged in to reply to this topic. Login to reply