3

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?

Vadim Ovchinnikov
  • 13,327
  • 5
  • 62
  • 90
veljasije
  • 6,722
  • 12
  • 48
  • 79
  • 1
    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

2 Answers2

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

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
2

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459