Question about tinyint, smallint datatype.

Hi, I just created a table with

create table X (
var1  tinyint,
var2 smallint)

I then check the table properties with sp_help X and what I saw is the var1 column only has length of 1 and var2 has length of 2.  

I know tinyint is 1 byte so it goes up to 255 and small int is 2 bytes so it goes up to 2^8 number I think.  Will SQL server increase my field length automatically?
fylix0000Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
the LENGTH you see is only the number of BYTES this column will use for storing the values, not the number of digits.

the same "error" is often done when seeing the TEXT data type with always lenght 16. there, 16 is only the size of the pointer data that is stored inline in the rows, the actual data (up to 2 GB) is stored apart.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Actually, small int goes 2^15-1, so that's up to 32767.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Brian CroweConnect With a Mentor Database AdministratorCommented:
bigint

Integer (whole number) data from -2^63 (-9223372036854775808) through 2^63-1 (9223372036854775807). Storage size is 8 bytes.

int

Integer (whole number) data from -2^31 (-2,147,483,648) through 2^31 - 1 (2,147,483,647). Storage size is 4 bytes. The SQL-92 synonym for int is integer.

smallint

Integer data from -2^15 (-32,768) through 2^15 - 1 (32,767). Storage size is 2 bytes.

tinyint

Integer data from 0 through 255. Storage size is 1 byte.

>Will SQL server increase my field length automatically?

No you are stuck with these limitations unless you change the datatype of the field
0
 
fylix0000Author Commented:
"the LENGTH you see is only the number of BYTES this column will use for storing the values, not the number of digits." so basically even though I see 1 in the field length, that does not mean i can only insert number 1-9, but rather any number within the limit.

BriCrowe, I was afraid I am retricted to the length I can insert in, in this case only number 1 to 9, I would have no problem if the small int limit at 2^15 - 1 (32,767).   Though thanks for explain it in detail.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
Wow... so the link I provided was of no help to you, eplaining the data types?
0
 
fylix0000Author Commented:
Sorry Leew :( , the question I put out only worth 100 points and I am more asking about the field lenght and not the data type information I saw in the database and angelIII's answer is what I was looking for.  Your link is very good at explain the data type in detail but I gave  BriCrow part points mainly becuase for the time he took type out in detail.  I hope you do not think I did not appricate your helps.
0
 
Lee W, MVPTechnology and Business Process AdvisorCommented:
ok... I thought you understood that 2 didn't mean two digits because you said:
> I know tinyint is 1 byte so it goes up to 255

That's why I said 32767 was the maximum...
At the same time I thought you might be confused about the value
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.