Without seeing code, it is pretty hard to say conclusively what is happening. Although, most likely the IDENTITY
value is being cached, causing gaps in the value after SQL Server is restarted. See http://stackoverflow.com/questions/17587094/identity-column-value-suddenly-jumps-to-1001-in-sql-server for some good answers and info about that.
A simple INT
field can hold values up to 2,147,483,647. You can actually start the identity value at -2,147,483,648, giving a full 32 bits of values. 4 Billion distinct values. I doubt very much you're going to run out of values to use. Assuming your application is consuming 1,000 values for each actual row added, you'd need to be creating nearly 12,000 rows per day every day to run out of IDs in 6 months assuming you started the IDENTITY
value at 0, and were using an INT. If you were using a BIGINT, you would have to wait 21 million centuries before you ran out of values if you wrote 12,000 rows per day, consuming 1,000 "values" per row.
Having said all that, if you wanted to use BIGINT
as the identity field data type, there is certainly nothing wrong with that. That'll give you for all intents-and-purposes, a limitless supply of values to use. The performance difference between an INT and a BIGINT is practically non-existent on modern 64-bit hardware, and highly preferable over for-instance using NEWID()
to generate GUIDs.
If you wanted to manage your own values for the ID column, you could create a key table, and provide a pretty bulletproof way of doing that using one of the methods shown in the answers on this question: Handling concurrent access to a key table without deadlocks in SQL Server
The other option, assuming you're using SQL Server 2012+, would be to use a SEQUENCE
object to get ID values for the column. However, you'd need to configure the sequence to not cache values. For example:
CREATE SEQUENCE dbo.MySequence AS INT START WITH -2147483648 INCREMENT BY 1 NO CACHE;
In answer to your boss' negative perception of "high" numbers, I would say what difference does it make? Assuming you use an INT
field, with an IDENTITY
, you could in fact start the IDENTITY
at 2147483647
and "increment" the value by -1
. This would make absolutely no difference to the memory consumption, performance, or disk space used since a 32 bit number is 4 bytes, no matter if it is 0
or 2147483647
. 0
in binary is 00000000000000000000000000000000
when stored in a 32-bit signed INT
field. 2147483647
is 01111111111111111111111111111111
- both numbers take precisely the same amount of space, both in memory, and on disk, and both require precisely the same amount of CPU operations to process. It is far more important to get your application code designed correctly than to obsess about the actual number stored in a key field.