0

I need an auto increment for the PK such that if the table's primary keys are 1, 2, 3, 4, 5, 6, then the next key should be 7 and then 8 and so on but if they keys are 1, 2, 3, 6 then the next key should be 4 and then 5 and then 7 and so on.

If there is no way to set the auto increment like this, is there at least a way to generate the number 4 using a SQL query in case the keys are 1,2,3,6.

The reason I need this is because a lot of a data gets stored into my db by the minute and it will keep deleting from the middle as well. The ID will hence reach 1 million in no time... and it will have a lot of numbers missing from the middle which i really want to avoid.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Neville Nazerane
  • 6,622
  • 3
  • 46
  • 79
  • Databases are built for large amounts of data. I wouldn't worry about this. – Oliver Nov 26 '13 at 14:19
  • 1
    If the field is a primary key then why does it matter if there are missing numbers? You are not using the field for anything significant other then uniquely identifying the row, right? – Linger Nov 26 '13 at 14:19
  • 2
    There is nothing wrong with reaching 1 million. What's the problem? INT holds up to 2 billion values. If that's not enough, you could start at -2 billion and hold 4 billion values. If that's still not enough, you could use BIGINT. The number of positive values BIGINT could hold would take much longer than your lifetime even if you generated thousands per second. – Aaron Bertrand Nov 26 '13 at 14:20
  • With `INT`, starting at 1, you get **over 2 billion** possible rows. With `BIGINT`, you get **922 quadrillion** (922 with 15 zeros - 922'000 billions). If you insert a row **every second**, you need 66.5 **years** before you run out of `INT` values... with `BIGINT`, if you insert **one thousand rows** every second, you need a mind-boggling **292 million years** before you hit the 922 quadrillion limit .... good enough for you? Stop worrying about "recycling" ID's - a really really bad idea to begin with.... – marc_s Nov 26 '13 at 15:05

2 Answers2

4

No - AutoIncrement is intentionally designed to keep IDs unique at the risk of not being sequential.

I would even avoid using triggers or something to "pack" the missing numbers - you run the risk of two processes trying to insert the same number, performance problems by inserting in the middle of your (presumably) clustered index, etc.

If you're concerned about running out of values, use bigint - it can store 9 trillion trillion values - more than you'd ever be able to consume even if you deleted millions of records a day.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
  • It is not about running out of values. My if the ID is 1200000 instead of 70, wont if use up more data over all? – Neville Nazerane Nov 27 '13 at 11:15
  • 1
    @NevilleNazerane no - an `int` will use 4 bytes (and a `bigint` 8 bytes) regardless of the value. – D Stanley Nov 27 '13 at 14:13
  • @DStanley not true if SQL Server row compression is being used but this wouldn't be a good reason for attempting the OPs requirement anyway. – Martin Smith Sep 18 '16 at 11:19
1

As all the Commentators said, you shouldn't worry about this. A good SQL-Database System can handle this.

but, if you really want to do it that way, have this link: Find the smallest unused number in SQL Server

and combine it's information with an insert trigger

Community
  • 1
  • 1
BigM
  • 678
  • 1
  • 17
  • 28