# Datatypes In Your Mind

• Revenant (5/6/2014)

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.

As per my observation, below mentioned code returns error ,

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

`SELECT @b1 + @b2;`

but, following code returns 5

`DECLARE @b1 AS BIT = 1,`

` @b2 AS BIT = 1;`

`SELECT @b1 + 4;`

`SELECT @b2 + 4;`

what's say on this

As per my observation, below mentioned code returns error ,

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

`SELECT @b1 + @b2;`

but, following code returns 5

`DECLARE @b1 AS BIT = 1,`

` @b2 AS BIT = 1;`

`SELECT @b1 + 4;`

`SELECT @b2 + 4;`

what's say on this

In the first example

`SELECT @b1 + @b2;`

you are attempting addition of two bit values (@b1 and @b2), which is not allowed

In the second example

`DECLARE @b1 AS BIT = 1,`

` @b2 AS BIT = 1;`

`SELECT @b1 + 4;`

`SELECT @b2 + 4;`

you are adding an integer (4) to the bit values. In this case, an implicit conversion from bit to integer takes place, so the addition is successful

• martin.whitton (5/7/2014)

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:

:-Dtinybit in a where clause?:-D

• Koen Verbeeck (5/5/2014)

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

Need more caffeine.

+ 1 to that. Apostrophes can be important eh?!

• Koen Verbeeck (5/5/2014)

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

Need more caffeine.

Misread too, considering those that can store store... :smooooth:

Igor Micev,My blog: www.igormicev.com

• Just a note on the tinyint - to store negatives in one byte the rightmost bit of the byte (111111 0) would need to be used to sign it - thus reducing the range to 127 - i guess that's why they didn't allow it.

Slightly off topic but excited to know something for a change & had to say it 😀

• naas2005 (5/8/2014)

Just a note on the tinyint - to store negatives in one byte the rightmost bit of the byte (111111 0) would need to be used to sign it - thus reducing the range to 127 - i guess that's why they didn't allow it.

Slightly off topic but excited to know something for a change & had to say it 😀

It doesn't reduce the range at all, it just shifts it. The range would still be 256 values. The unsigned range is 0 to 255 inclusive and the signed range -127 to 127 inclusive.

Tom

• LOL CORRECTION - I should have said "positive" range.

• Tinyint is an underappreciated datatype. I leverage it a lot for small range keys like marital_status or for things like service_years. A lot of developers punt and always use 4 byte Integer, and some deliberately choose 8 byte BigInt for everything. Disk space is cheap, but column size matters on a table with a billion rows; especially when pages are spooling out to memory, tempdb, transaction log, or backup tape.

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

• TomThomson (5/8/2014)

naas2005 (5/8/2014)

Just a note on the tinyint - to store negatives in one byte the rightmost bit of the byte (111111 0) would need to be used to sign it - thus reducing the range to 127 - i guess that's why they didn't allow it.

Slightly off topic but excited to know something for a change & had to say it 😀

It doesn't reduce the range at all, it just shifts it. The range would still be 256 values. The unsigned range is 0 to 255 inclusive and the signed range -127 to 127 inclusive.

-128 to 127, that is 256 numbers

• Carlo Romagnano (5/8/2014)

TomThomson (5/8/2014)

naas2005 (5/8/2014)

Just a note on the tinyint - to store negatives in one byte the rightmost bit of the byte (111111 0) would need to be used to sign it - thus reducing the range to 127 - i guess that's why they didn't allow it.

Slightly off topic but excited to know something for a change & had to say it 😀

It doesn't reduce the range at all, it just shifts it. The range would still be 256 values. The unsigned range is 0 to 255 inclusive and the signed range -127 to 127 inclusive.

-128 to 127, that is 256 numbers

Yes, -128. I must learn to connect brain to fingers before typing comments.

Tom

• Andy Warren (5/6/2014)

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, thanks for the thoughtful reply. I can appreciate that creating a good QotD can be difficult (since this is not the first one that I have seen with some controversy:-)). All tinyint/bit controversy aside, it was a well-written question with an amusing story that was more entertaining than just a simple "What is the smallest..." question.

One of these days, I'll have to send in a QotD of my own. Hmm, what to ask???

• Andy,

I'm curious about why you chose the title "Datatypes In Your Mind".

Did you expect this TinyInt vs Bit question of the day to create some controvery and debate?

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

• 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 to that.

because of the wording in the question, the title, the documentation, etc... Bit fits what was asked the most.

The BUS driver never asked what was the real number smallest data type you could do maths with.

That has a different answer.

• Eric, I wish I could say I was that smart! No, it was just me being a little silly, riffing on "tiny bubbles".

Viewing 15 posts - 46 through 60 (of 64 total)

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