Datatypes In Your Mind

Question

Post reply

Datatypes In Your Mind

Andy Warren

SSC Guru

Points: 119902
More actions
May 5, 2014 at 8:32 pm

#148535

Comments posted to this topic are about the item Datatypes In Your Mind
Andy
SQLAndy - My Blog!
Connect with me on LinkedIn
Follow me on Twitter

Viewing 15 posts - 1 through 15 (of 64 total)

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

stephen.long.1 SSCrazy Points: 2582 More actions · Answer 1

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.

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

Good question, thanks!

Did think a bit for a second:-D

Jeff Moden SSC Guru Points: 1004329 More actions · Answer 3

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

Koen Verbeeck SSC Guru Points: 259105 More actions · Answer 4

Misread the question and thought the data type was supposed to store negatives as well...

Need more caffeine.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Carlo Romagnano SSC-Insane Points: 22669 More actions · Answer 5

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.

The correct answer IS bit!

+1

Andy, are you hungry?

free_mascot One Orange Chip Points: 27169 More actions · Answer 6

Good One, Thanks!

---------------------------------------------------
"Thare are only 10 types of people in the world:
Those who understand binary, and those who don't."

rhythmk SSCertifiable Points: 7271 More actions · Answer 7

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 😉

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

Raghavendra Mudugal SSChampion Points: 10658 More actions · Answer 8

Good one Andy, understanding those variable/s from different angle.

hmm, according to the driver, it is tinyint, but the passenger (me) who answered, bit, does not agrees with the driver... and it leads to heated argument... driver loses is mind and lost control.. and we all know the rest what is going to happen... :w00t: and... now I am scared of SQL.

-//edit; fixed the typo and added "(me)"

ww; Raghu
--
The first and the hardest SQL statement I have wrote- "select * from customers" - and I was happy and felt smart.

SQLDoubleG Hall of Fame Points: 3100 More actions · Answer 9

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 number

declare @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.

Stuart Davies SSCoach Points: 19505 More actions · Answer 10

Koen Verbeeck (5/5/2014)
Misread the question and thought the data type was supposed to store negatives as well...
Need more caffeine.

+1 - and first day back after a long weekend

-------------------------------Posting Data Etiquette - Jeff Moden [/url]Smart way to ask a question
There are naive questions, tedious questions, ill-phrased questions, questions put after inadequate self-criticism. But every question is a cry to understand (the world). There is no such thing as a dumb question. ― Carl Sagan
I would never join a club that would allow me as a member - Groucho Marx

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 11

pmadhavapeddi22

SSCertifiable

Points: 5306

May 6, 2014 at 3:11 am

#1710972

I have also answered 'BIT' 😉

pmadhavapeddi22 SSCertifiable Points: 5306 More actions · Answer 12

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

Initially I thought "bit" can be used for true/false and tinyint is the right answer for QOTD but reading microsoft documentation i got confused and answered as BIT :hehe:

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

Cannot consider bit to be an integer datatype, more like a flag, kind of "fingers or no fingers" instead of "how many fingers".

😎

DECLARE @FIRST_BIT BIT = 1;

DECLARE @NEXT_BIT BIT = -1;

SELECT

CASE

WHEN @FIRST_BIT = @NEXT_BIT THEN 'OOPS, NOT AN INTEGER TYPE!'

ELSE 'A PROPER INTEGER TYPE!'

END AS RESULTS

mkeustermans Old Hand Points: 348 More actions · Answer 14

You can assign any value to a bit that is not equal to 0 and it will convert it to 1, a 0 obviously remains a 0.

Compare the following:

DataType: bit Value Range:1,0 or null Physical Storage: 1 bit

DataType: tinyint Value Range: 0-255 Physical Storage: 1 byte

So IMHO it is safe to say that bit is the smallest exact number integer data type, both in the range of values it holds as well as physical storage.