We are working on a web application, not yet accessible to users. My boss noticed that newly created records get an ID of over 10 000, even though we only have under 100 records in the table. She assumed that the web interface for some reason creates over a 100 times more temporary records than actual ones (and deletes them) and that this can lead us to running out of range within a few months of release.
I don't think she is correct about the cause of the ID inflation (the colleague who can answer this is on vacation, so we don't know for sure), but let's assume that she is. She said that she'd hate to use a bigint column, and that she'd like us to stop autoincrementing the ID column and write server-side code which chooses the first "unused" integer and uses it as an ID.
I am a computer science grad student with little practical experience, filling a junior developer role. She has years of experience of managing all of our organisation's databases, and designing most of them. I think that she's incorrect in this case, that a bigint ID is nothing to be afraid of, and that mimicking the DBMS functionality smells of an antipattern. But I don't trust my judgment yet.
What are the arguments for and against each position? What bad things can happen if we use a bigint, and what are the dangers of reinventing the
wheel autoincrementing functionality? Is there a third solution which is better than either one? What could her reasons be for wanting to avoid an inflation of ID face values? I'm interested in hearing about pragmatic reasons too - maybe bigint IDs work in theory, but cause headaches in practice?
The application is not expected to handle very large amounts of data. I doubt that it will reach 10 000 actual records within the next few years.
If it makes any difference, we are using Microsoft SQL server. The application is written in C# and uses Linq to SQL.
Thank you, I found the existing answers and comments interesting. But I'm afraid you misunderstood my question, so they contain what I wanted to know.
I'm not really concerned about the real reason for the high IDs. If we can't find it on our own, I could ask a different question. What I'm interested in is to understand the decision process in this case. For this, please assume that the application will be writing 1000 records per day, then deleting 9999 of them. I'm almost sure this is not the case, but this is what my boss believed when she made her request. So, under these hypothetical circumstances, what would be the pros and cons of either using bigint or writing our own code which will assign IDs (in a way which reuses the IDs of already deleted records, to ensure there are no gaps)?
As for the actual reason, I strongly suspect that this is because we once wrote code to import data from another database, as a proof of concept that a later migration can be done to a certain extent. I think my colleague actually created several thousand records during the import and later deleted them. I have to confirm if this was actually the case, but if it is, there is not even need for action.