• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 216
  • Last Modified:

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.

0
dotnet0824
Asked:
dotnet0824
  • 4
  • 2
1 Solution
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
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 & ArchitectCommented:
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
 
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Raja Jegan RSQL Server DBA & ArchitectCommented:
>> 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
 
dotnet0824Author Commented:
thanks a lot
0
 
Raja Jegan RSQL Server DBA & ArchitectCommented:
Welcome..
0

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.

  • 4
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now