Bit Casting

  • Xedni

    SSCertifiable

    Points: 6947

    Comments posted to this topic are about the item Bit Casting

    Executive Junior Cowboy Developer, Esq.[/url]

  • This was removed by the editor as SPAM

  • antony-688446

    Ten Centuries

    Points: 1221

    Arrggghhh....

    Got lost in my 1's and 0's and pressed the wrong option. Knew the right answer, just picked the wrong 'un.

  • Koen Verbeeck

    SSC Guru

    Points: 258965

    Easy one, thanks!

    Need an answer? No, you need a question
    My blog at https://sqlkover.com.
    MCSE Business Intelligence - Microsoft Data Platform MVP

  • Toreador

    SSChampion

    Points: 11257

    Might have been a bit trickier if it didn't come down to knowing how -5 was treated

  • Hany Helmy

    SSChampion

    Points: 13488

    Nice one, thanx.

  • akljfhnlaflkj

    SSC Guru

    Points: 76202

    Good reminder thanks.

  • Sean Lange

    SSC Guru

    Points: 286536

    Good question. Let's not forget that this is NOT a real representation of a boolean. A bit in sql server can have 3 values with NULL being the third.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • RK Mandava

    SSCarpal Tunnel

    Points: 4153

    Nice Question, Thanks

  • stephen.long.1

    SSCrazy

    Points: 2577

    Nice question, Gabe, although it would have been better if you had included more options so finding the solution didn't just boil down to "how is -5 converted". One possibility would be 0,0,0,1,0,1,0, where only '1' and 1 are converted to a 1. You could have also included converting -1 and/or '-1' to a bit, because some languages (VB6 comes to mind)treat Boolean data types as either 0 or -1, because -1 is 11111111111111111111111111111111 in binary.

  • DonlSimpson

    SSCertifiable

    Points: 6774

    Interesting that the text 'false' evaluates to 0. I would have guessed that any non-empty string would evaluate to 1.

    Don Simpson



    I'm not sure about Heisenberg.

  • Xedni

    SSCertifiable

    Points: 6947

    Stewart "Arturius" Campbell (12/17/2014)


    Nice one, thanks Gabe.

    Have used this before, where the values were stored as true / false strings in the mainframe.

    No. I actually found out the "true"/"false" functionality after the fact (thank god). I just thought it warranted mentioning. I ran into this when I added a primary key on a table variable I was doing some testing with, which had a bit column as the PK. Instead of overflowing when I added a third value, it did a PK violation rather than an overflow

    Executive Junior Cowboy Developer, Esq.[/url]

  • Xedni

    SSCertifiable

    Points: 6947

    DonlSimpson (12/18/2014)


    Interesting that the text 'false' evaluates to 0. I would have guessed that any non-empty string would evaluate to 1.

    Just to add insult to injury, if you try to cast any string other than "true" or "false" to a bit, you'll get a conversion failure.

    select cast('Hello' as bit)

    There are a lot of really good reasons NOT to use those strings when assigning bit values. But it's interesting to know, none the less.

    Executive Junior Cowboy Developer, Esq.[/url]

  • Marcia J

    SSCertifiable

    Points: 5649

    antony-688446 (12/17/2014)


    Arrggghhh....

    Got lost in my 1's and 0's and pressed the wrong option. Knew the right answer, just picked the wrong 'un.

    I've done that before. 😛

  • TomThomson

    SSC Guru

    Points: 104773

    Sean Lange (12/18/2014)


    Good question. Let's not forget that this is NOT a real representation of a boolean. A bit in sql server can have 3 values with NULL being the third.

    It actually is a real representation of a boolean. Being able to be NULL instead of having a value is an inherent property of booleans in the real world (and also in the world of mathematics).

    As far as booleans are concerned there are two values that they can have. It's also possible that for a particular boolean you don't know the value. That is represented n SQL by an SQL value not constrained not to be null. Of course there are times when one wants to refer to a boolean value where the value, whatever it may be, will always be known. This can of course be expressed perfectly well an attribute or a row in a table, using the bit type qualified by NOT NULL , but can't be expressed for an SQL local variable (local variablies are a bit of a pigs breakfast in SQL semantics for that reason amongst others).

    So SQL has two different sorts of bits: both sorts have two values, and only two values, so as far as their values are concerned they are real representations of a boolean value. One of those two sorts is such that it is always known what its value is, so is what one might call an ideal boolean. The other is a the real world boolean, which admits the possiblility that at any given time the value may not be known.

    So I don't think that it's realistic to say that bit is not a real representation of a boolean.

    There are some rather nice examples of boolean values that are unknown in mathematics: for example suppose F is a partial recursive function, P is the proposition "if F is applied to 0 at 1.00 am using a computer capable of 10**10 Mflop/s the computation will terminate by 5pm this afternoon". The tructh value for proposition is a boolean, it is either true or false, but it may be impossible to determine which it is before someone has tried the experiment. So until the question has been resolved by experiment, the value is unknown and SQL will correctly represent that situation by responding NULL instead of delivering a 1 or 0 value to a request for the value of P until such time as the experiment has been carried out and the result inserted into the database. And there are nastier examples: suppose the proporsition is "if F is applied to 0 the computation will eventually terminate with a valid result provided enough computational power is supplied for enough time"; it is a proven fact of computational theory (since over three quarters of a century ago) that for some values of F the value of the corresponding proposition P can never be known unless P is true, and it can only be known that it's true if someone has actualy carried out the computation and it terminated with a valid result, and it is impossible in general to determine whether any particular F is or is not such an F (independent proofs were made by Gödel - the incompleteness theorem - and Turing - the halting problem - and by others), so a proper representation of a boolean must allow it to be NULL instead of having a value unless there are very solid grounds for claiming that its value is always known.

    Tom

Viewing 15 posts - 1 through 15 (of 19 total)

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