Calculating datatype limits i.e. SMALLINT - If SMALLINT is used to defined a user ID, how many users can populate a database before the limit for a table has been reached?
Greetings:
Just as the question states, we have been using SMALLINT to define USER IDs throught our schema. The concern has been raised that unless the schema is changed so that USER IDs are of type INT, we will exceed capacity.
Two questions:
1) How does it become obvious that available space will be in danger? Are we talking about the risk for one particular table (i.e. CompanyUsers) or does this mean there is a risk in every table that contains the USER ID?
2) How do I go about calculating the amount of space available to show exactly where the database stands?
Thanks
Just as the question states, we have been using SMALLINT to define USER IDs throught our schema. The concern has been raised that unless the schema is changed so that USER IDs are of type INT, we will exceed capacity.
Two questions:
1) How does it become obvious that available space will be in danger? Are we talking about the risk for one particular table (i.e. CompanyUsers) or does this mean there is a risk in every table that contains the USER ID?
2) How do I go about calculating the amount of space available to show exactly where the database stands?
Thanks
Hello John500,
Well smallint has a range of -32768 to 32767 so assuming that you are only using positive numbers for your user id then you have a maximum capacity of 32767 users. Now without knowing how this relates to your circumstances you could consider this to be a reasonable number or not. An int datatype has a capacity of +/- 2147483647 so would give you a much larger potential.
If you determine that 32767 is an insufficient number then you do have the option to double this to 65536 by using negative numbers and not changing the datatypes of your tables or you would have to change the datatype of every column in every table that holds a userID value.
As for calculating where it stands, you need to simply work out how many records there are in your relevant table (allowing of course for the fact that this may be an identity field and that there may be "missing numbers").
Regards,
TimCottee
Well smallint has a range of -32768 to 32767 so assuming that you are only using positive numbers for your user id then you have a maximum capacity of 32767 users. Now without knowing how this relates to your circumstances you could consider this to be a reasonable number or not. An int datatype has a capacity of +/- 2147483647 so would give you a much larger potential.
If you determine that 32767 is an insufficient number then you do have the option to double this to 65536 by using negative numbers and not changing the datatypes of your tables or you would have to change the datatype of every column in every table that holds a userID value.
As for calculating where it stands, you need to simply work out how many records there are in your relevant table (allowing of course for the fact that this may be an identity field and that there may be "missing numbers").
Regards,
TimCottee
Put negatives on my first numbers...not sure why that didnt' copy over.
HI
1) it is no a space that you should concern on , it is the max value your id can handle , after this value is exceeded , you will get negative numbers.
You can do the change easily by using the ALTER TABLE command, since you are only extending it , it's OK.
Do not forget to update any other table in which the user ID is a Foreign Key.
2)
See below , you can support up to 32,767 generated IDs for users in smallint
When using int you got 2,147,483,647
Exact number data types that use integer data.
bigint
Integer (whole number) data from -2^63 (-9,223,372,036,854,775,80
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.
Remarks
1) it is no a space that you should concern on , it is the max value your id can handle , after this value is exceeded , you will get negative numbers.
You can do the change easily by using the ALTER TABLE command, since you are only extending it , it's OK.
Do not forget to update any other table in which the user ID is a Foreign Key.
2)
See below , you can support up to 32,767 generated IDs for users in smallint
When using int you got 2,147,483,647
Exact number data types that use integer data.
bigint
Integer (whole number) data from -2^63 (-9,223,372,036,854,775,80
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.
Remarks
ASKER
When I issue the following statement:
SELECT COUNT(USER_ID) FROM USERS
i get:
-----------
8818
(1 row(s) affected)
Is it correct to say there is no problem because 32,767 - 8818 = 23,949 (available ids) ??
>>> allowing of course for the fact that this may be an identity field and that there may be "missing numbers"
How could there be any missing numers? The results of the select statement (8,818) shown above would surely include any identity fields - right ?
SELECT COUNT(USER_ID) FROM USERS
i get:
-----------
8818
(1 row(s) affected)
Is it correct to say there is no problem because 32,767 - 8818 = 23,949 (available ids) ??
>>> allowing of course for the fact that this may be an identity field and that there may be "missing numbers"
How could there be any missing numers? The results of the select statement (8,818) shown above would surely include any identity fields - right ?
John500,
Assuming that you haven't deleted any users, or had any inserts fail then your calculation would be accurate, a double check with:
Select Max(User_ID) From Users
Would confirm that of course.
If you have a relatively low growth in user volume over time and you have approx 20000 available user ids then you would be comfortable in keeping the fields with their current datatype. If you expect your user base to increase significantly over time then you should review it.
TimCottee
Assuming that you haven't deleted any users, or had any inserts fail then your calculation would be accurate, a double check with:
Select Max(User_ID) From Users
Would confirm that of course.
If you have a relatively low growth in user volume over time and you have approx 20000 available user ids then you would be comfortable in keeping the fields with their current datatype. If you expect your user base to increase significantly over time then you should review it.
TimCottee
ASKER
Tim,
The result of Max(User_ID) is :
30,651
>> If you have a relatively low growth in user volume ...
It's not user volume as much as it is turn-over rate of the IDs.
If I were to delete all unused IDs this would cause an inability to track history on previous users? Hence, it stands reason the data type needs to change.
Is there any systematic approach that comes to mind where the current ID will not change in spite of the fact that every table using the ID will change from SMALLINT to INT ?
Thanks
The result of Max(User_ID) is :
30,651
>> If you have a relatively low growth in user volume ...
It's not user volume as much as it is turn-over rate of the IDs.
If I were to delete all unused IDs this would cause an inability to track history on previous users? Hence, it stands reason the data type needs to change.
Is there any systematic approach that comes to mind where the current ID will not change in spite of the fact that every table using the ID will change from SMALLINT to INT ?
Thanks
ASKER CERTIFIED SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
SOLUTION
membership
Create a free account to see this answer
Signing up is free and takes 30 seconds. No credit card required.
ASKER
Thank you sirs
INT is over 2 billion....Integer (whole number) data from 2^31 (2,147,483,648) through 2^311 (2,147,483,647).