Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17


Question about tinyint, smallint datatype.

Posted on 2006-06-09
Medium Priority
Last Modified: 2012-05-05
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?
Question by:fylix0000
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
LVL 96

Expert Comment

by:Lee W, MVP
ID: 16873738
Actually, small int goes 2^15-1, so that's up to 32767.
LVL 96

Expert Comment

by:Lee W, MVP
ID: 16873744
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 280 total points
ID: 16873752
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.
Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 120 total points
ID: 16873785

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


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.


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


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

Author Comment

ID: 16873798
"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.
LVL 96

Expert Comment

by:Lee W, MVP
ID: 16873823
Wow... so the link I provided was of no help to you, eplaining the data types?

Author Comment

ID: 16873906
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.
LVL 96

Expert Comment

by:Lee W, MVP
ID: 16873945
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

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

721 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question