January 28, 2013 at 8:44 pm
Comments posted to this topic are about the item BIT Primary Key
January 28, 2013 at 8:44 pm
Thanks for the question. +1
January 28, 2013 at 9:27 pm
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
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
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
Link to my Blog Post --> www.SQLPathy.com[/url]
Follow me @Twitter
January 28, 2013 at 11:39 pm
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
Nice straight-forward question. Thanks.
January 29, 2013 at 1:46 am
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).
January 29, 2013 at 2:48 am
+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
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
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
Thanks for the easy but thoughtful question!
January 29, 2013 at 4:24 am
Nice easy question.
But I agree with what demonfox and Hugo said about thw explanation.
Tom
January 29, 2013 at 4:36 am
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