Why are there gaps in my IDENTITY column values?

by

The identity property on a column does not guarantee the following:

Uniqueness of the value – Uniqueness must be enforced by using a PRIMARY KEY or UNIQUE constraint or UNIQUE index.

Consecutive values within a transaction – A transaction inserting multiple rows is not guaranteed to get consecutive values for the rows because other concurrent inserts might occur on the table. If values must be consecutive then the transaction should use an exclusive lock on the table or use the SERIALIZABLE isolation level.

Consecutive values after server restart or other failures –SQL Server might cache identity values for performance reasons and some of the assigned values can be lost during a database failure or server restart. This can result in gaps in the identity value upon insert. If gaps are not acceptable then the application should use a sequence generator with the NOCACHE option or use their own mechanism to generate key values.

Reuse of values – For a given identity property with specific seed/increment, the identity values are not reused by the engine. If a particular insert statement fails or if the insert statement is rolled back then the consumed identity values are lost and will not be generated again. This can result in gaps when the subsequent identity values are generated.

Also,

If an identity column exists for a table with frequent deletions, gaps can occur between identity values. If this is a concern, do not use the IDENTITY property. However, to make sure that no gaps have been created or to fill an existing gap, evaluate the existing identity values before explicitly entering one with SET IDENTITY_INSERT ON.

Also, Check the Identity Column Properties & check the Identity Increment value. Its should be 1.

enter image description here

More Related Contents:

  1. Sql Mathematical calculations [closed]
  2. How to display data in JOIN, and return null value? [closed]
  3. How to create a SQL Server function to “join” multiple rows from a subquery into a single delimited field? [duplicate]
  4. How to deal with SQL column names that look like SQL keywords?
  5. SQL transpose full table
  6. Truncate (not round) decimal places in SQL Server
  7. How to run a stored procedure in sql server every hour?
  8. How do I see active SQL Server connections?
  9. SQL Server LIKE containing bracket characters
  10. UPDATE if exists else INSERT in SQL Server 2008 [duplicate]
  11. Why do you create a View in a database?
  12. How to run a stored procedure every day in SQL Server Express Edition?
  13. How to copy a huge table data into another table in SQL Server
  14. Calculate the number of records for each date between 2 dates
  15. “Order By” using a parameter for the column name
  16. T-SQL: How to use parameters in dynamic SQL?
  17. DISTINCT for only one column
  18. How to compare datetime with only date in SQL Server
  19. SQL server select distinct rows using most recent value only
  20. Split words with a capital letter in sql
  21. Measuring Query Performance : “Execution Plan Query Cost” vs “Time Taken”
  22. Generate random int value from 3 to 6
  23. RODBC Temporary Table Issue when connecting to MS SQL Server
  24. What is the internal representation of datetime in sql server?
  25. Eliminate and reduce overlapping date ranges
  26. scope_identity vs ident_current
  27. SQL Server 2005 – Export table programmatically (run a .sql file to rebuild it)
  28. Convert bigint to datetime
  29. SQL SERVER DATETIME FORMAT
  30. Insert all values of a table into another table in SQL

Leave a Comment