The BIT data type

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

    Adam Sottosanti

  • 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

  • Simple and easy question.

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

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

  • Good question and learnt something new today.

  • Nice easy one thanks for the point

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

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

  • Good question 🙂

    M&M

  • 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

  • Thanks for the interesting question!

  • 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

  • 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

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

  • 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