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 (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 68
Comments posted to this topic are about the item BIT Primary Key
Yogeshwar Phull
Yogeshwar Phull
SSC Eights!
SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)SSC Eights! (952 reputation)

Group: General Forum Members
Points: 952 Visits: 680
Thanks for the question. +1
demonfox
demonfox
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1711 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
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1302 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
SSCrazy
SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)SSCrazy (2.4K reputation)

Group: General Forum Members
Points: 2434 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
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2510 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
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10982 Visits: 7326
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 (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)SSCertifiable (5.5K reputation)

Group: General Forum Members
Points: 5487 Visits: 2382
Nice straight-forward question. Thanks.
Hugo Kornelis
Hugo Kornelis
SSChampion
SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)SSChampion (13K reputation)

Group: General Forum Members
Points: 13141 Visits: 12176
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
Mr or Mrs. 500
Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)Mr or Mrs. 500 (512 reputation)

Group: General Forum Members
Points: 512 Visits: 312
+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