Is it possible to force IDENTITY column to recalculate its seed property , when it reach maximum value for defined data type, to fill gaps in ID's.
Let say like this way, I have a column of TINYINT data type which can hold up values to maximum of 255. When column is filled with data to maximum ID possible, I delete one row from middle, let's say ID = 100.
Question is, can I force IDENTITY to fill that missing ID at the end?
You can reseed the IDENTITY (set a new seed), but it will NOT be able to magically find the missing values.....
The reseeded IDENTITY column will just keep handing out new values starting at the new seed - which means, at some point, sooner or later, collisions with already existing values will happen
Therefore, all in all, reseeding an IDENTITY really isn't a very good idea .... Just pick a data type large enough to handle your needs.
With a type INT, starting at 1, you get over 2 billion possible rows - that should be more than sufficient for the vast majority of cases. With BIGINT, you get roughly 922 quadrillion (922 with 15 zeros - 9'220'000 billions) - enough for you??
If you use an INT IDENTITY starting at 1, and you insert a row every second, you need 66.5 years before you hit the 2 billion limit ....
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 ....
Read more about it (with all the options there are) in the MSDN Books Online.