Solved

Question about tinyint, smallint datatype.

Posted on 2006-06-09
8
612 Views
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?
0
Comment
Question by:fylix0000
8 Comments
 
LVL 95

Expert Comment

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

Expert Comment

by:Lee W, MVP
ID: 16873744
0
 
LVL 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 70 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.
0
Edgartown IT Case Study

Learn about Edgartown's quest to ensure the safety and security of the entire town's employee and citizen data. Read the case study!

 
LVL 34

Assisted Solution

by:Brian Crowe
Brian Crowe earned 30 total points
ID: 16873785
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
 

Author Comment

by:fylix0000
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.
0
 
LVL 95

Expert Comment

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

Author Comment

by:fylix0000
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.
0
 
LVL 95

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
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

735 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