-3

(sorry about my english...)

I've this table

CREATE TABLE [dbo].[Expedients]
(
    [IdExpedient] [numeric](18, 0) IDENTITY(1,1) NOT NULL,
    ...
)

If I get all idExpedients on a 8.000 registers table using

select idExpedient 
from expdients 
order by idexpedients desc

calculate de difference between one idExpedient and the following one (and then filter for difference up 100) I get this:

idExp   Dif.
348376  20000
328375  10000
318345  10000
308308  9998
298121  9997
288111  9941
278098  9999
267924  9979
257752  9994
247702  9951
237694  9999
227657  9997
217639  10000
207604  9981
197576  10001
187285  9999
177285  9998
167271  9999
157268  9976
147269  10001
137268  10001
127266  10000
117248  9988
107248  10001
97181   9984
87167   9990
77102   9746
67067   9999
57038   10000
47027   9999
37013   9997
26981   10000
16979   9998

Does someone know what is happening?

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

1 Answers1

1

IDENTITY only guarantees that that numbers will always be greater than the previous, not that they will necessarily be 1 greater. I believe that the number is cached in memory, and not written out to disc every time one is allocated (writing to disk on every allocation could have quite an overhead if you are doing an insert into tableWithIdentity....select from otherTable).

My observation is that when it loads the next number from disk, it updates the disk with that number plus another number, say next + 1000 (as Oracle does with sequences and their Cache value). When it has allocated all those IDENTITY values (1000 of them in this example) it updates the disk again adding another 1000.

This means that if the server shuts down badly at some points in time then the last actually allocated number is (as guaranteed) less than the next number to be allocated, but not 1 less. Thus you can get gaps.

Note that the number 1000 I have used for the cache size is not supported by any documentation anywhere. I just used it to make my point intelligible.

PS: see here. That answer says that unallocated numbers are always lost on a restart of the server.

Community
  • 1
  • 1
simon at rcl
  • 7,326
  • 1
  • 17
  • 24