The BIT data type

  • Adam Sottosanti

    Old Hand

    Points: 388

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

    Adam Sottosanti

  • Koen Verbeeck

    SSC Guru

    Points: 258950

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

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

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



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

    Good question 🙂

    M&M

  • TomThomson

    SSC Guru

    Points: 104772

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

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

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

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

    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)

You must be logged in to reply to this topic. Login to reply