0

I have a junction table UserStamps between the table Users and Stamps.

The UserStamps table has the following columns:

  • ID (bigint) [Identity 1,1]
  • UserId (int)
  • StampId (int)
  • Quantity (smallint)

Each user can have up to 150 stamp at a time but he can acquire/remove/exchange stamps.

By acquiring, a new row is added.

By removing, the row is deleted from the table but exchanging replace one stamp Id with the other.

My question is: By continuous adding/removing of rows the ID column will reach the overflow state and it is known that identity always counts from the last number and ignore gaps between numbers.

When the counter reaches maximum will it fill any gaps between the IDs if not how can I handle the overflow situation ?

Taher
  • 565
  • 1
  • 14
  • 32
  • 2
    worry about it when you get there. that's a 64-bit integer, you're going to be needing to be adding TONS of user stamps to get there. like 9,223,372,036,854,775,807 user stamps. – Darren Kopp Jul 10 '14 at 01:25
  • [{Link} is this answer relevant to your question?](http://stackoverflow.com/a/5305271/491243) and also [this](http://stackoverflow.com/questions/7674420/what-will-append-if-the-maximum-value-of-an-identity-column-is-reached). – John Woo Jul 10 '14 at 01:26
  • How many users and stamp records do you want to be able to handle? You have to have that number before you can pick the correct data type. – David Jul 10 '14 at 01:29
  • 3
    If you use a `BIGINT IDENTITY` starting at 1, and you insert **one thousand rows every second**, you need a mind-boggling **292 million years** before you hit the 922 quadrillion limit .... – marc_s Jul 10 '14 at 04:47

0 Answers0