SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


BIT Primary Key


BIT Primary Key

Author
Message
Thomas.Kovarik
Thomas.Kovarik
SSC Rookie
SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)SSC Rookie (46 reputation)

Group: General Forum Members
Points: 46 Visits: 68
Comments posted to this topic are about the item BIT Primary Key
Yogeshwar Phull
Yogeshwar Phull
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1038 Visits: 685
Thanks for the question. +1
demonfox
demonfox
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2771 Visits: 1192
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
Dineshbabu
Dineshbabu
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1610 Visits: 569
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..
Lokesh Vij
Lokesh Vij
SSCarpal Tunnel
SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)SSCarpal Tunnel (4.4K reputation)

Group: General Forum Members
Points: 4430 Visits: 1599
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
Guidelines for answers on Performance questions

Link to my Blog Post --> www.SQLPathy.com

Follow me @Twitter


Raghavendra Mudugal
Raghavendra Mudugal
SSCarpal Tunnel
SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)SSCarpal Tunnel (4.3K reputation)

Group: General Forum Members
Points: 4348 Visits: 2958
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 int
set @b = 777777
select convert(bit, @b)



ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
Stewart "Arturius" Campbell
Stewart "Arturius" Campbell
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24861 Visits: 7504
Nice, simple, back-to-basics question, thanks Thomas

And to demonfox for the clarification...

____________________________________________
Space, the final frontier? not any more...
All limits henceforth are self-imposed.
“libera tute vulgaris ex”
Ken Wymore
Ken Wymore
SSCertifiable
SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)SSCertifiable (7.9K reputation)

Group: General Forum Members
Points: 7904 Visits: 2418
Nice straight-forward question. Thanks.
Hugo Kornelis
Hugo Kornelis
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25129 Visits: 12585
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 MVP
Visit my SQL Server blog: http://sqlblog.com/blogs/hugo_kornelis
manik_anu
manik_anu
Right there with Babe
Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)Right there with Babe (791 reputation)

Group: General Forum Members
Points: 791 Visits: 319
+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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search