# The BIT data type

Old Hand

Points: 388

Comments posted to this topic are about the item The BIT data type

• Koen Verbeeck

SSC Guru

Points: 258985

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

• Gobikannan

SSCrazy

Points: 2735

Simple and easy question.

-----------------
Gobikannan

• Duncan Pryde

SSCertifiable

Points: 7956

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.

• Abi Chapagai

SSCrazy

Points: 2666

Good question and learnt something new today.

• Mike Is Here

Hall of Fame

Points: 3348

Nice easy one thanks for the point

• mtassin

SSC-Insane

Points: 23099

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.

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• mtassin

SSC-Insane

Points: 23099

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

--Mark Tassin
MCITP - SQL Server DBA
Proud member of the Anti-RBAR alliance.
For help with Performance click this link[/url]
For tips on how to post your problems[/url]

• M&M

SSC-Insane

Points: 21699

Good question 🙂

M&M

• TomThomson

SSC Guru

Points: 104773

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

• UMG Developer

SSChampion

Points: 13482

Thanks for the interesting question!

• SQLRNNR

SSC Guru

Points: 281252

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

• Richard Warr

SSCertifiable

Points: 6957

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

_____________________________________________________________________
MCSA SQL Server 2012

• Hugo Kornelis

SSC Guru

Points: 64685

Tom.Thomson (3/29/2011)

I wonder what happens with

`DECLARE @bit BIT`

``` SET @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.

Hugo Kornelis, SQL Server/Data Platform MVP (2006-2016)
Visit my SQL Server blog: https://sqlserverfast.com/blog/
SQL Server Execution Plan Reference: https://sqlserverfast.com/epr/

• TomThomson

SSC Guru

Points: 104773

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 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 19 total)