May 6, 2014 at 3:34 am
So it accurately holds zero, but zero is not a real number.....?
😎
May 6, 2014 at 3:38 am
raulggonzalez (5/6/2014)
Thanks for the question,I guess the author goes more this way
--the smallest exact number integer data type that can't support a negative numberdeclare @bit bit
set @bit = -1
Command(s) completed successfully.
declare @tinyint tinyint
set @tinyint = -1
Msg 220, Level 16, State 2, Line 5
Arithmetic overflow error for data type tinyint, value = -1.
so certainly bit datatype can get a negative value which will be transformed to 0 or 1.
🙂
Actually Bit does not store negative value only 0,1 & Null.You can check the value of that variable.
--rhythmk
------------------------------------------------------------------
To post your question use below link
https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help
🙂
May 6, 2014 at 3:45 am
It's been a while since I did math theory.
Zero is a whole number then, ipso facto, it is a real number.
May 6, 2014 at 4:04 am
Curses, I was hovering over "tinyint" for a good 30 seconds before deciding that no, "Bit" was probably the correct answer... 🙂
May 6, 2014 at 4:12 am
Eirikur Eiriksson (5/6/2014)
Cannot consider bit to be an integer datatype, more like a flag, kind of "fingers or no fingers" instead of "how many fingers"...
....probably you may want to use one more select statement after declaration to see what those two variables contains
ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.
May 6, 2014 at 4:20 am
rhythmk (5/6/2014)
raulggonzalez (5/6/2014)
Thanks for the question,I guess the author goes more this way
--the smallest exact number integer data type that can't support a negative numberdeclare @bit bit
set @bit = -1
Command(s) completed successfully.
declare @tinyint tinyint
set @tinyint = -1
Msg 220, Level 16, State 2, Line 5
Arithmetic overflow error for data type tinyint, value = -1.
so certainly bit datatype can get a negative value which will be transformed to 0 or 1.
🙂
Actually Bit does not store negative value only 0,1 & Null.You can check the value of that variable.
That's exactly what I said, but you still can assign a negative value to a BIT variable without getting an error. That doesn't happen for a tinyint, hence the right answer is tinyint and not bit.
May 6, 2014 at 4:36 am
But the questions states
"tell me the smallest exact number integer data type that can't support a negative number" - which is BIT - it can't support negatives as a -1 is converted to 1 - If the question asked which generates an error - tinyint would be the right answer
I answered Bit as well :hehe:
May 6, 2014 at 4:41 am
Fun story, nice question - shame about the sloppy execution.
I found myself in that position where I have to second-guess the question author. I knew it was either tinyint or bit, depending on Andy's interpretation. And I ended up picking the wrong choice.
One day I will have to start working on my mind-reading skills! 😉
May 6, 2014 at 5:42 am
Hugo Kornelis (5/6/2014)
Fun story, nice question - shame about the sloppy execution.I found myself in that position where I have to second-guess the question author. I knew it was either tinyint or bit, depending on Andy's interpretation. And I ended up picking the wrong choice.
One day I will have to start working on my mind-reading skills! 😉
I went through the same attempted mind-reading process, with the same result. As the BOL entry for BIT said "Integer data type 1, 0, or NULL" for SQL 2000 and the only change since has been to make it "An integer data type that can take a value of 1, 0, or NULL" (same in every release from 2005 to 2014) it seems pretty well established that bit is an integer data type, and no-one would claim that tinyint is smaller; on the other hand, surely an integer type is also a numeric type, and BOL was a bit confused about BIT and numeric for SQL 2008 R2 and earlier (back then it was an exact numeric, but it wasn't a numeric) and later BOL releases don't include it in the list of numeric types and have no list of exact numeric types so we don't get to discover whether it is an exact numeric or not :w00t:, so it didn't seem at all certain which way Andy would jump. On balance, "BIT" seemed more probable, but Andy had picked "TINYINT" instead.
So I too need to improve my mind-reading skills - or maybe we need to somehow get people to stop asking questions that require mind-reading ;-).
Tom
May 6, 2014 at 5:50 am
Apart from all that controversy debate about Bit or Tinyint data types, Andy got talent 🙂
he should work in the movies` industry 😎
Thanks & Best Regards,
Hany Helmy
SQL Server Database Consultant
May 6, 2014 at 6:40 am
The list shown here includes bit in the list of "exact numeric types".
http://msdn.microsoft.com/en-us/library/ms187752.aspx (Data Types (Transact-SQL))
Besides this reference page (including the ones for other version of SQL), I find not many other references that bit is a numeric datatype. It can store the integer values 0 and 1, but it just isn't numeric. E.g. can't use the add operator, and you can use 'false' and 'true' in the conversion.
Bit is a special datatype that should not have been listed on the page there. If you look at the left menu, you even see that the link to the bit page is directly under the Data Types heading.
So it stores integer values, but it is not numeric.
But again, this is my interpretation. Reading BOL and other resources doesn't make it more clear, but just more confusing.
May 6, 2014 at 6:46 am
May 6, 2014 at 6:47 am
According to Celko, "Machine-level things like a BIT or BYTE data type have no place in SQL."
😎
May 6, 2014 at 6:49 am
Jeff Moden (5/5/2014)
Even though I strongly [font="Arial Black"]disagree [/font]that BIT is an integer datatype simply because it can't be used in certain bits of math, such as a SUM(), I have to agree with Stephen based soley on what the Microsoft documentation says... and it does say..."[font="Arial Black"]An integer data type [/font]that can take a value of 1, 0, or NULL."
Anyone that answered "BIT" should get the point... if points matter to anyone.;-)
+1 🙂
May 6, 2014 at 6:51 am
I also found myself debating between the bit and the tiny integer. I know MSDN says that a bit is an exact numeric that supports a 0 or 1, but I don't think of it that way because of the math restrictions with it. It also says that SQL optimizes the storage of bits, so it came down to a guess between the two.
Viewing 15 posts - 16 through 30 (of 64 total)
You must be logged in to reply to this topic. Login to reply