Identity field problem

• masadmin

  Grasshopper

  Points: 23

  More actions

  January 7, 2004 at 7:15 am

  #81863

  I have a simple table with an identity field that acts as the primary key. The field is set to increment by 1, but sometimes misses out numbers (missed 16 to 20 then 22 and 23). Any ideas what may be causing this?

• Frank Kalis

  SSC Guru

  Points: 111183

  More actions

  January 7, 2004 at 7:32 am

  #488955

  CREATE TABLE MyIdentity(

  id INT IDENTITY(1,1) NOT NULL,

  some_other VARCHAR(10)

  )

  GO

  BEGIN TRAN

  INSERT INTO MyIdentity VALUES('Erster')

  COMMIT TRAN

  BEGIN TRAN

  INSERT INTO MyIdentity VALUES('Zweiter')

  ROLLBACK TRAN

  BEGIN TRAN

  INSERT INTO MyIdentity VALUES('Dritter')

  COMMIT TRAN

  SELECT * FROM MyIdentity

  DROP TABLE MyIdentity

  GO

  results in

  id          some_other

  ----------- ----------

  1           Erster

  3           Dritter

  (2 row(s) affected)

  In almost any case there will sooner or later be gaps. But that shouldn't be a problem.

  --
  Frank Kalis
  Microsoft SQL Server MVP
  Webmaster: http://www.insidesql.org/blogs
  My blog: http://www.insidesql.org/blogs/frankkalis/[/url]