Reaching the end of the value pool for autogenerated (IDENTITY) fields

Most database support autogenerate for an INT field, mostly used as the key for that table. It sometimes is set named id for short.

how much time before the program lets say c# int and the database INT value no longer support each other, or how long before the INT value is used up, or how large is this auto generated id field for a database, lets say sqlite?

When you try to insert a new record, and the data base auto generated row id is used up, what happens; no more insert?

thanks

[EDIT]

Do you look at the ID values of your tables once in a while?

Most people don't know that if for some reason you app does alot of insert and delete, then a new ID value is generated each time you insert. is seems that databases does not reuse deleted ID, so in that case a database with just 1000 records can use up that value if the app does alot of insert and deleteing!!!

The reason i asked this question is this, normally in apps you frequently make use of the ID val开发者_高级运维ue, to keep track of records; i do always.

lest say i did somthing like this

int record_id= reader("id").value;

when will i get an overflow error? is the database INT the same as int in all programing environment?

I don't know about sqlite but for the SQL Server the maximum values look like this:

max int (32 bit) = 2,147,483,647
max bigint (64 bit) = 9,223,372,036,854,775,807

If at some point you've used up the value range then an attempt to insert a new record will fail. It will say the following:

Arithmetic overflow error converting IDENTITY to data type int. Arithmetic overflow occurred.

Again, have no idea about sqlite but in SQL Server the failed transactions will also consume a value from the pool. If you attempt an insert and the containing transaction is rolled back then the generated IDENTITY value will not be returned to the pool. If you have transactions failing regularly then that will also waste a certain ( though very small) percentage of the values.

Most likely sqlite works in a similar way.

The maximum size for an INT in SQLite is 8-bytes, which is a very, very big number. I find it hard to imagine an application which used SQLite where you could actually "run out" of INTs. You would have to be running some very large data-warehousing system for this to happen, which you are not going to be doing with SQLite. So don't worry!