sql server datatype

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.

dotnet0824Asked:
Who is Participating?
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
More info about these datatypes:

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
Introducing Cloud Class® training courses

Tech changes fast. You can learn faster. That’s why we’re bringing professional training courses to Experts Exchange. With a subscription, you can access all the Cloud Class® courses to expand your education, prep for certifications, and get top-notch instructions.

 
dotnet0824Author Commented:
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
 
dotnet0824Author Commented:
thanks a lot
0
 
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome..
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.