Post reply

BIT Primary Key

  • January 28, 2013 at 8:44 pm

    #273451

    Comments posted to this topic are about the item BIT Primary Key

  • January 28, 2013 at 8:44 pm

    #1581877

    Thanks for the question. +1

  • January 28, 2013 at 9:27 pm

    #1581880

    nice and easy ..

    thanks for the question ..

    Also, about the explanation

    The row with Comment 'row 3' is rejected by database engine because decimal integer 2 (which is binary 10) results in an attempt to insert 1 (0 was truncated because it is the least significant digit)

    I don't think, it works like that ; BIT datatype stores only 1,0 or NULL ; (True/False goes as 1 /0). even inserting 7777777 or -1 gets inserted as 1 .

    Apart from that ,unrelated to the question, as per my view indexing a bit column is really not necessary, as the range of value is limited ..

    ~ demonfox
    ___________________________________________________________________
    Wondering what I would do next , when I am done with this one :ermm:

  • January 28, 2013 at 10:17 pm

    #1581887

    demonfox (1/28/2013)

    I don't think, it works like that ; BIT datatype stores only 1,0 or NULL ; (True/False goes as 1 /0). even inserting 7777777 or -1 gets inserted as 1 .

    As demonfox said, any integer value other than 0 will be stored as 1. There is no reference in MSDN to prove given value will be converted to binary and then it will be truncated.

    Is this the internal behaviour of sql server?

    --
    Dineshbabu
    Desire to learn new things..

  • January 28, 2013 at 10:18 pm

    #1581888

    Easy question for the day!

    I agree with the explanation by "demonfox", here is the BOL link for BIT datatype:

    http://msdn.microsoft.com/en-us/library/ms177603.aspx

    It clearly says "any non-zero value will be converted to 1". I could not find the concept of the number being converted into binary and than getting truncated. Can anyone pour some light on what is happening internally?

    ~ Lokesh Vij

    Guidelines for quicker answers on T-SQL question[/url]
    Guidelines for answers on Performance questions

    Link to my Blog Post --> www.SQLPathy.com[/url]

    Follow me @Twitter

  • January 28, 2013 at 11:39 pm

    #1581899

    demonfox (1/28/2013)

    nice and easy ..

    thanks for the question ..

    Also, about the explanation

    The row with Comment 'row 3' is rejected by database engine because decimal integer 2 (which is binary 10) results in an attempt to insert 1 (0 was truncated because it is the least significant digit)

    I don't think, it works like that ; BIT datatype stores only 1,0 or NULL ; (True/False goes as 1 /0). even inserting 7777777 or -1 gets inserted as 1

    ... maybe it works in that way....

    as it does implicit conversion, so the engine has the ability to make sure the bit data types gets the data only what it is suppose to take.

    declare @b-2 int

    set @b-2 = 777777

    select convert(bit, @b-2)

    ww; Raghu
    --
    The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

  • January 29, 2013 at 12:40 am

    This was removed by the editor as SPAM

  • January 29, 2013 at 1:35 am

    #1581928

    Nice straight-forward question. Thanks.

  • January 29, 2013 at 1:46 am

    #1581935

    A good and creative question, hampered by some flaws in the explanation.

    The reason the number 2 is converted to bit 1 is not because of its bit pattern, but for the simple reason that "Converting to bit promotes any nonzero value to 1." (see http://msdn.microsoft.com/en-us/library/ms177603.aspx).

    Other than that, the explanation itself was okay, but I don't understand (1) why the same reference was included three times - -was this supposed to be three different references? If not, including it once would have been better for readability; and (2) why the references are all to an outdated version of Books Online (the "(v=SQL.90)" at the end of the link force Books Online to show the SQL 2005 version; similarly, you can use (v=SQL.100), (v=SQL.105), or (v=SQL.110) for the SQL2008, SQL2008R2, and SQL2012 version -provided the same page exists in that version. Or, my recommendation, you can leave out the version completely and always get the version from the most current Books Online.

    (The last remark is targeted at all QotD authors, not just Thomas - it keeps surprising me how many questions inlcude references to old versions, and I never understand why people don't bother to link to a more recent version).

    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/

  • January 29, 2013 at 2:48 am

    #1581959

    +1....:-D, normally while insert the data the primary key allow unique data..... but we dont thing about the datatype of primary key column.... its really tricky......

    Manikandanps

    -----Move fast as possibel ------------------

    Manik
    You cannot get to the top by sitting on your bottom.

  • January 29, 2013 at 3:24 am

    #1581974

    A great question thanks, straightforward but needing a little thought.

    Also... re: explanation... what demonfox and Hugo said 😉

    _____________________________________________________________________
    [font="Comic Sans MS"]"The difficult tasks we do immediately, the impossible takes a little longer"[/font]

  • January 29, 2013 at 3:31 am

    #1581976

    GOOD QUESTION..

    +1

    _______________________________________________________________
    To get quick answer follow this link:
    http://www.sqlservercentral.com/articles/Best+Practices/61537/

  • January 29, 2013 at 4:07 am

    #1581999

    Thanks for the easy but thoughtful question!

  • January 29, 2013 at 4:24 am

    #1582011

    Nice easy question.

    But I agree with what demonfox and Hugo said about thw explanation.

    Tom

  • January 29, 2013 at 4:36 am

    #1582015

    Good question, not too difficult but required taking into account PK, check constraint and data type which meant it took me a few seconds.

    Obviously some valid criticisms of the explanation but otherwise - more like this please 🙂

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

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