Solved

sql server datatype

Posted on 2010-01-01
214 Views
Hi,
I have a customerID column . Can i declare it as a tinyint . It would be a autoincremnting identity column.
I can see that tiny int stores

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

Does it mean that customerID's   256,257,258 ...  cant be inserted and it only accepts till 255 number.
can it be explained what does 0 to 255 storage means for tinyint

and also storage for int with an example which is easy to understand.

0
Question by:dotnet0824

LVL 57

Expert Comment

tinyint stores values from 0 to 255 only.
smallint stores values from -2^15(-32,768) to 2^15-1 (32,767)
int stores values from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647)

Hence go for either smallint or int datatype which supports more than 255 unique values..
0

LVL 57

Expert Comment

http://msdn.microsoft.com/en-us/library/ms187745.aspx

>> Does it mean that customerID's   256,257,258 ...  cant be inserted and it only accepts till 255 number.

Yes.. It will not allow more than 255..
If you want more than 255, then go for either smallint or int datatype as mentioned earlier..

>> can it be explained what does 0 to 255 storage means for tinyint

0 to 255 means that it can hold values from 0 to 255 and not more than that..
and hence you cannot store more than 255.
Hope this clarifies.
0

Author Comment

what does int stores values from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) mean?

how can I know that. can a small example be explained  like you did for  tinyint.
0

LVL 57

Accepted Solution

>> what does int stores values from -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) mean?

An int datatype column can store values in the range between -2,147,483,648 and 2,147,483,647..

>> can a small example be explained  like you did for  tinyint.

Just try this:

create table test ( a tinyint, b int);
insert into test (a, b) values ( 1, 1111111);
insert into test (a, b) values ( 255, 2147483647);
insert into test (a, b) values ( 256, 2147483648);
insert into test (a, b) values ( 255, -2147483648);

3rd Insert statement alone will fail whereas the other INSERT's would pass.
Just try inserting more records both within the range specified above and below to understand by yourself.
0

Author Closing Comment

thanks a lot
0

LVL 57

Expert Comment

Welcome..
0

Featured Post

Hi all, It is important and often overlooked to understand “Database properties”. Often we see questions about "log files" or "where is the database" and one of the easiest ways to get general information about your database is to use “Database p…
In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this sixth video of the Xpdf series, we discuss and demonstrate the PDFtoPNG utility, which converts a multi-page PDF file to separate color, grayscale, or monochrome PNG files, creating one PNG file for each page in the PDF. It does this via a c…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.