none
Identity on a Primary Column Generating wrong sequence

    Question

  •  I have created an Employee table with the following columns and constraints: EmpId(Primary Key, Identity(1,1),not null) EmpName, EmpJob etc. Then I tried to insert Employee values from an ASPX page throuh ADO.NET middleware, unfortunately it had generated a wrong sequence of EmpId of Employee and the sequence is 2 3 4 5 6 7 9 17 18. I dont know why it is behaving like that,so please help me.
    Sunday, July 13, 2014 7:52 AM

Answers

  • If you want employee numbers to be contiguous, you should do it this way:

    BEGIN TRANSACTION

    SELECT @empid = isnull(MAX(empid), 0) + 1 FROM Employees WITH (UPDLOCK)

    INSERT Employees (EmpId, EmpName, EmpJob, ...)
       VALUES(@EmpId, @EmpName, @EmpJob, ...)

    COMMIT TRANSACTION

    The drawback with this solution is that if you have 1000 HR staff sitting and entering employees like crazy, they will be serialised at this point, as they will be blocked on the SELECT statement until it is their turn to insert.

    IDENTITY is designed to avoid this problem. And a core feature of that design is that if a transaction is rolled back, the number not restored but dropped and lost forever.

    Overall, using IDENTITY can incur some maintenance headaches, and the easiness you save in coding when inserting data is minimal. Thus, only use IDENTITY when high concurrency is a concern.

    It is worth adding, that in most cases there is little reason to require contiguous numbers. It does occur with financial matters, and in some countries there may also be legal requirements for things like order ids. But in many cases an id is just a surrogate key with no meaning in itself, why gaps are no cause for concern.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 13, 2014 10:50 AM
  • It can also be due to some constraint violations as illustrated below

    declare @test table
    (
    id int identity(1,1) not null,
    val varchar(50)   unique
    )
    
    insert @test (val)
    values ('test')
    insert @test (val)
    values ('test1')
    --this will cause a constraint violation and will cause a gap in identity value
    insert @test (val)
    values ('test1')
    insert @test (val)
    values ('test2')
    
    select *
    from @test


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, July 13, 2014 8:19 AM

All replies

  • It may be that you may have deleted some intermediate data. Please keep in mind that next insertion will continue with next identity value and will not reclaim a deleted records identity value.

    Actually you dont need to worry about gaps caused by deletion as you can still retrieve the records in the order you want.


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, July 13, 2014 8:15 AM
  • It can also be due to some constraint violations as illustrated below

    declare @test table
    (
    id int identity(1,1) not null,
    val varchar(50)   unique
    )
    
    insert @test (val)
    values ('test')
    insert @test (val)
    values ('test1')
    --this will cause a constraint violation and will cause a gap in identity value
    insert @test (val)
    values ('test1')
    insert @test (val)
    values ('test2')
    
    select *
    from @test


    Please Mark This As Answer if it helps to solve the issue Visakh ---------------------------- http://visakhm.blogspot.com/ https://www.facebook.com/VmBlogs

    Sunday, July 13, 2014 8:19 AM
  • Hi

    there are two possible reasons

    1) That transaction may rollback(same as constrain violation), for which the value are skipped

    2) those values may deleted by any user

    i have shown you with example below by (V2 - delete, V3- rollback, second v1 -constrain violation )

    create table t12
    (id int identity(1,1),
    value1 varchar(30) unique)
    go
    insert into t12(value1)
    select 'v1'
    
    insert into t12(value1)
    select 'v2'
    delete from t12 where value1='v2'
    
    begin tran
    insert into t12(value1)
    select 'v3'
    rollback
    
    insert into t12(value1)
    select 'v4'
    
    insert into t12(value1)
    select 'v1'
    
    insert into t12(value1)
    select 'v6'
    
    select * from t12


    • Edited by SaravanaC Sunday, July 13, 2014 8:30 AM
    Sunday, July 13, 2014 8:24 AM
  • If a transaction rolled back, then IDENTITY leaves a gap since it is not rolled back.

    Currently even server restart will cause a gap in IDENTITY.

    If you need contiguous sequence, you have to use the age-old MAX + 1 method.

    See discussion of this topic here:

    http://social.msdn.microsoft.com/Forums/sqlserver/en-US/cf63d145-7084-4371-bde0-eb3b917c7163/identity-big-jump-100010000-a-feature?forum=transactsql


    Kalman Toth Database & OLAP Architect SQL Server 2014 Design & Programming
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012










    Sunday, July 13, 2014 8:25 AM
  • If you want employee numbers to be contiguous, you should do it this way:

    BEGIN TRANSACTION

    SELECT @empid = isnull(MAX(empid), 0) + 1 FROM Employees WITH (UPDLOCK)

    INSERT Employees (EmpId, EmpName, EmpJob, ...)
       VALUES(@EmpId, @EmpName, @EmpJob, ...)

    COMMIT TRANSACTION

    The drawback with this solution is that if you have 1000 HR staff sitting and entering employees like crazy, they will be serialised at this point, as they will be blocked on the SELECT statement until it is their turn to insert.

    IDENTITY is designed to avoid this problem. And a core feature of that design is that if a transaction is rolled back, the number not restored but dropped and lost forever.

    Overall, using IDENTITY can incur some maintenance headaches, and the easiness you save in coding when inserting data is minimal. Thus, only use IDENTITY when high concurrency is a concern.

    It is worth adding, that in most cases there is little reason to require contiguous numbers. It does occur with financial matters, and in some countries there may also be legal requirements for things like order ids. But in many cases an id is just a surrogate key with no meaning in itself, why gaps are no cause for concern.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 13, 2014 10:50 AM
  • >> I have created an Employee table with the following columns and constraints: EmpId(Primary Key, Identity(1,1),not null) EmpName, EmpJob etc. <<

    Why did you post narrative instead of DDL? I bet you have no idea how to write DDL or even what is does. You did not even get that useless narrative right! 

    Why do you have only one employee – that is what your singular able name tells us. An SQL programmer would have named it Personnel, an abstract set! The jobs are a separate set of entities would be related to personnel by a third table. 

    IDENTITY is not a column; it is a table property that counts the number of physical insertion attempts (not even successes!) to the physical table. It has no place in a logicla data model. 

    >> Then I tried to insert Employee values from an ASPX page through ADO.NET middleware, unfortunately it had generated a wrong sequence of EmpId of Employee and the sequence is 2 3 4 5 6 7 9 17 18. I don't know why it is behaving like that, so please help me.<<

    Why did you tell us this? We are the “database guys” and we do not care where you get the data. You also do not know that SEQUENCE is a reserved word for a feature in SQL. 

    Please stop programming until you have read even one intro books on RDBMS. You are dangerous to your employer and we cannot give you an education in a fourm.


    --CELKO-- Books in Celko Series for Morgan-Kaufmann Publishing: Analytics and OLAP in SQL / Data and Databases: Concepts in Practice Data / Measurements and Standards in SQL SQL for Smarties / SQL Programming Style / SQL Puzzles and Answers / Thinking in Sets / Trees and Hierarchies in SQL

    Sunday, July 13, 2014 6:14 PM
  • Please stop programming until you have read even one intro books on RDBMS. You are dangerous to your employer and we cannot give you an education in a fourm.

    Please stop responding to posts until you have learnt some manner. You very are dangerous to your employer in case you would get in contact with your customer.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se
    Sunday, July 13, 2014 6:23 PM