If this is your first visit, be sure to
check out the FAQ by clicking the
link above. You may have to register
before you can post: click the register link above to proceed. To start viewing messages,
select the forum that you want to visit from the selection below.

Unanswered: Limit of IDENTITY columns

Hi to all,

Most identity columns I have seen are specified as LONG data types but I want to ask if what happens if it reaches its limit. I mean, what if it has used up all its values to use? Although they generate over 2 billion values ( the max value of a LONG data type), it is quite possible that they can be used up? don't you think?

I agree, I would never reach the 9 quintillion rows, but what will happen if I am using a front-end application created in VB6?

When trying to hold the auto_increment field, do you think I should stray away from the Long data type? The long data type is about 2 billion ( I think, correct me if I am wrong). If ever the value in the auto_increment fields exceeds on the max of the Long data type, it just makes sense to change to another data type right> Currency? Double ? Maybe String would do.....

did you read the reference?
it looks as if the reecomendation form MySQL is NOT to use BIGINT, unless you know the values are below the Integer threshold.. in which case surely you'd use the integer datatype to start with. As I read that reference it suggests if you really need a number of that size use MySQL double which
like MySQL Bigint is a 64 bit IEEE value. but you will have to do some work handling rounding errors inherent in VB

it expressly states
1) There is no such thing as a 32 bit unsigned integer in VB, so data must be stored in a double, which is actually a floating-point datatype, therefore, be careful to make sure that any value that you will be passing to MySQL is rounded off before you post it. The use of an UNSIGNED INT with VB6 is not reccomended, but you may have some luck with the larger integers of VB.NET.

2) BIGINT cannot be used with ADO, as it will cause errors, you can use the MyODBC option of 16384 (convert BIGINT to INT) to make them funnel down, but from what I understand your BIGINT is then limited to the size of an INT anyway. If a BIGINT is brought into a long, you will be ok up to the limit if the long datatype, after which your data will be corrupted.

whole number rounding issues shouldn't be an issue with VB6's doubles until the count gets greater than 15 significant digits. That's a thousand trillion records. Or, a million billion records. Either way you look at it, that a bunch of 'em.

If you were to use currency, you could stretch that out to 28 significant digits. Wich is ten trillion times larger than a whole number double.

Lou
使大吃一惊"Lisa, in this house, we obey the laws of thermodynamics!" - Homer Simpson
"I have my standards. They may be low, but I have them!" - Bette Middler
"It's a book about a Spanish guy named Manual. You should read it." - Dilbert

did you read the reference?
it looks as if the reecomendation form MySQL is NOT to use BIGINT, unless you know the values are below the Integer threshold.. in which case surely you'd use the integer datatype to start with. As I read that reference it suggests if you really need a number of that size use MySQL double which
like MySQL Bigint is a 64 bit IEEE value. but you will have to do some work handling rounding errors inherent in VB

it expressly states
1) There is no such thing as a 32 bit unsigned integer in VB, so data must be stored in a double, which is actually a floating-point datatype, therefore, be careful to make sure that any value that you will be passing to MySQL is rounded off before you post it. The use of an UNSIGNED INT with VB6 is not reccomended, but you may have some luck with the larger integers of VB.NET.

2) BIGINT cannot be used with ADO, as it will cause errors, you can use the MyODBC option of 16384 (convert BIGINT to INT) to make them funnel down, but from what I understand your BIGINT is then limited to the size of an INT anyway. If a BIGINT is brought into a long, you will be ok up to the limit if the long datatype, after which your data will be corrupted.