Datatypes In Your Mind

Question

Datatypes In Your Mind

Viewing 15 posts - 31 through 45 (of 64 total)

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

Eric M Russell SSC Guru Points: 125561 More actions · Answer 1

Sorry - you were wrong
Correct answer: TinyInt
Explanation: There are four data types that are integer/exact number: bigint, int, smallint, and tiny int. The ranges of those can be found here http://technet.microsoft.com/en-us/library/ms187745.aspx.
The only one that doesn't support a negative number is tinyint.

Here is what MSDN has to say about the Bit datatype:

bit (Transact-SQL)
An integer data type that can take a value of 1, 0, or NULL.
http://technet.microsoft.com/en-us/library/ms177603.aspx

A Bit is an integer at the most atomic level. Whether it's supported by ANSI SQL, integer based T-SQL functions, or J.C. is a moot point.

Andy, you provided 'Bit' as a possible choice, so what is the reasoning behind excluding it in favor of TinyInt?

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

RLilj33 SSCrazy Points: 2153 More actions · Answer 2

stephen.long 56048 (5/5/2014)
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))
The definition of bit, shown here, calls it "An integer data type that can take a value of 1, 0, or NULL."
http://msdn.microsoft.com/en-us/library/ms177603.aspx (bit (Transact-SQL))
Since:
- a bit can only store 0 and 1, so it is an "exact number integer data type", as specified in the question
- bit doesn't support negative numbers
- 0-1 is a smaller range than 0-255
The correct answer should be bit.

+1

sknox SSChampion Points: 12388 More actions · Answer 3

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.;-)

They matter to me... a bit.

Andy Warren SSC Guru Points: 119902 More actions · Answer 4

I'm just catching up on the morning mail and reading this thread, thinking how hard it is to write a good question and how much I enjoy the good natured ribbing I get for my not uncommon missteps!

Looking at the question and the answer, I wish I had omitted bit, or made a more convincing question that ruled out bit. I didn't though, because of a mental block - bits are bits to me and not numbers. Dumb, obvious, and maybe even logical!

I also had to smile about the mind reading. This was supposed to be a fairly easy question! Nuance, always nuance. It's easy to make the tinyest (bit-iest?) mistake in framing it to lead to ambiguity. The fun part - for me - is that when it's ambiguous you're not sure if it's me being devious or me missing an angle you see! For those who don't know me my intent isn't to aggravate you in a bad way, but rather to challenge you and I try to do that in a way that is fair, though I get that some of the ways I try may not seem that way.

To the points, yes - it IS about points. The intent of question was to get tinyint as an answer, but I'll grant that I have no great logic in the question or otherwise that would make bit wrong and if anything it shows a deep understanding of the topic. Up to Steve on the points, but I'll recommend that bit answers get points also.

Thanks for trying the question and the candid feedback, more in the pipeline.

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

sknox SSChampion Points: 12388 More actions · Answer 5

Eirikur Eiriksson (5/6/2014)
According to Celko, "Machine-level things like a BIT or BYTE data type have no place in SQL."
😎

This is why it's a very good thing Celko isn't the only voice in the SQL server community.

A bit is not a "machine-level thing". It is a single-digit binary number. You may as well say that the number 3 has no place in arithmetic.

A byte, being an arbitrary length of binary digits, could be considered a machine-level thing, but it's no more or less arbitrary than "tinyint" which is, to all intents and purposes, a synonym for "byte".

Thomas Abraham SSChampion Points: 10761 More actions · Answer 6

1. Bit is an exact numeric per link cited above.

2. Bit can't even hold a negative value.

3. There are no data types smaller than bit.

Thus, the correct answer should be BIT, as selected by, as of this writing, 43% of answerers.

[font="Verdana"]Please don't go. The drones need you. They look up to you.[/font]
Connect to me on LinkedIn

ssimmons 2102 SSCommitted Points: 1634 More actions · Answer 7

funny I'm sitting here looking at the MSDN for 2012 and under exact numerics I see Bit listed there.

Revenant SSC-Forever Points: 42467 More actions · Answer 8

Documentation implies that this should work, but it does not:

DECLARE @b1 AS BIT = 1,

@b2 AS BIT = 1;

DECLARE @int AS INT = @b1 + @b2;

SELECT @b1 + @b2;

Bits just are not evaluated as numeric values.

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 9

rhythmk (5/6/2014)
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.;-)
Jeff, I think point matters and that is why we have point system for QoTD 🙂
Anyway Andy has not mentioned in his explanation that why bit is not correct answer as per the question 😉

Heh... and here I thought it was supposed to be used to identify the areas that one needed more study in. 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Eirikur Eiriksson SSC Guru Points: 182911 More actions · Answer 10

Jeff Moden (5/6/2014)
rhythmk (5/6/2014)
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.;-)
Jeff, I think point matters and that is why we have point system for QoTD 🙂
Anyway Andy has not mentioned in his explanation that why bit is not correct answer as per the question 😉
Heh... and here I thought it was supposed to be used to identify the areas that one needed more study in. 😛

I think Andy has been a tiny bit naughty:-D

Andy Warren SSC Guru Points: 119902 More actions · Answer 11

Maybe best reply so far!

Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Revenant SSC-Forever Points: 42467 More actions · Answer 12

Andy Warren (5/6/2014)
Maybe best reply so far!

Well, we did get some discussion on this one, didn't we?

TomThomson SSC Guru Points: 104773 More actions · Answer 13

Eirikur Eiriksson (5/6/2014)
According to Celko, "Machine-level things like a BIT or BYTE data type have no place in SQL."
😎

Well, if he allows tinyint and smallint and int and bigint why can't he allow one_bit_int as well? And if he can, why can't we call it "bit"? Standards are fine, but pretending that there's some principle that says a single bit integer is unacceptable is just nonsense. ANd if he objects to tinyint (8 bit unsigned int) why? Any decent type system (of course no-one would suggest that SQL had a decent type system, but that's a failing of SQL not virtue) will allow unsigned integers (natural numers) as well as signed integers, and since there is no way we can implement a type that supports all possible integers (we can't build unbounded storage) there is no point in pretending that there isn't a storage issue that makes it useful to have integers oif vairous sizes and excluding tinyint (it's badly named, of course - it's an unsigned 8 bit value, that's fair enough, but the other types called int - smallint, int, bigint - are all signed).

Tom

rhythmk SSCertifiable Points: 7271 More actions · Answer 14

Jeff Moden (5/6/2014)
rhythmk (5/6/2014)
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.;-)
Jeff, I think point matters and that is why we have point system for QoTD 🙂
Anyway Andy has not mentioned in his explanation that why bit is not correct answer as per the question 😉
Heh... and here I thought it was supposed to be used to identify the areas that one needed more study in. 😛

You are right sir.We usually do more research and study from QoTD.It is really nice way to learn.

--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
🙂

Louisa Whitton SSCrazy Points: 2577 More actions · Answer 15

Eirikur Eiriksson (5/6/2014)
I think Andy has been a tiny bit naughty:-D

Is a "tiny bit" a "bit" that will only store zeroes? 😀

My personal feeling is that "bit" is not an integer data type, but then the documentation says otherwise - and that could never be wrong could it? :unsure: