Circular IDENTITY numbers in column

Question

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..... it'll just keep handing out new values starting at the new seed - which means, at some point, collisions with already existing values **will happen** - not a very good idea .... — marc_s, Apr 08 '15 at 12:54
You confirm my assumptions :) I was just curious if it is possible, but you gave me real answer. Thanks! — veljasije, Apr 08 '15 at 12:59

score 2 · Answer 1 · answered Apr 08 '15 at 12:49

2

Yes,you can do this:

set identity_insert on;
insert into table(id) value(100)

--set it off

set identity_insert off;

Identity Insert

answered Apr 08 '15 at 12:49

vhadalgi

7,027
6
38
67

Can it do by itself, without my action? I know that it is possible like your way, but I assume that is not allowed to do it automatically – veljasije Apr 08 '15 at 12:53
1

No it doesn't adjust by itself look at the [link](http://stackoverflow.com/questions/21824478/reset-identity-seed-after-deleting-records-in-sql-server) for more info – vhadalgi Apr 08 '15 at 13:00

score 2 · Accepted Answer · answered Apr 08 '15 at 13:00

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.

Circular IDENTITY numbers in column

2 Answers2