Autoincrement column value goes higher randomly

Question

I am struggling with very strange issue. Thanks in advance to read my problem and help me.

we have recently launched one mobile application. We have use WCF web service and entity framework. We have use Sql server express 2014 as database.

Now We have one user table where we store user basic detail. We have one auto increment column in this table which is primary key too.

Expected behavior of auto increment column is when new user registered , Column value should be increment by 1 unit and generally it is happening that way.

Once we have 175 users in our database and then when new user registered , New value of "USERID" column jump to 1169!!!

See below screen shot of our database table. enter image description here

Then Again values are start incrementing by 1 unit like 1169,1170,1171 .....

Now Above problem found again after USERID 1296.. see below screen shot of our database table.

enter image description here

Thanks,

Ronak

Are you deleting records, and then running further inserts? Or - is this starting always from an empty, Truncated table? — Dave Bish, May 22 '15 at 11:47
@Dave Bish..There is no delete operation logic we have performed in this table — Ronak Shah, May 22 '15 at 11:48
@RonakShah Note that if you begin a transaction, insert a record, then rollback, the IDs are "lost" — xanatos, May 22 '15 at 11:49
have you checked the database to see if the Identity column for the auto Increment is set to a valid integer to increment by.. also have you tried running the SQL Command to `ReSeed` that column..? — MethodMan, May 22 '15 at 11:51
@xanatos: Yes but we have make sure that this issue is not because of rollback.. — Ronak Shah, May 22 '15 at 11:57

score 2 · Answer 1 · answered May 22 '15 at 12:24

I found solution.

From SQL Server 2012 version, when SQL Server instance is restarted, then table's Identity value is jumped and the actual jumped value depends on identity column data type. If it is integer (int) data type, then jump value is 1000 and if big integer (bigint), then jump value is 10000. From our application point of view, this increment is not acceptable for all the business cases specially when the value shows to the client. This is the special case/issue ships with only SQL Server 2012 and older versions have no such issue.

Solutions

If we are not interested in this so called feature, then we can do two things to stop that jump.

•Using Sequence •Register -t272 to SQL Server Startup Parameter

Using Sequence

First, we need to remove Identity column from tables. Then create a sequence without cache feature and insert number from that sequence. The following is the code sample:

Hide   Copy Code
CREATE SEQUENCE Id_Sequence
    AS INT
    START WITH 1
    INCREMENT BY 1
    MINVALUE 0
    NO MAXVALUE
   NO CACHE

Register -t272 to SQL Server Startup Parameter

Open SQLServer configuration manager from your server. Select SQL Server 2012 instance there right client and select Properties menu. You will find a tabbed dialog window. You select start up parameters tab from there and register -t272. Then restart SQL Server 2012 instance again and see the difference:

Solution Source - http://www.codeproject.com/Tips/668042/SQL-Server-2012-Auto-Identity-Column-Value-Jump-Is

Thanks,

Ronak

score -1 · Answer 2 · answered May 22 '15 at 11:59

If its possible, try to Truncate Table to reset de ID counter of the table. (carefull because you will lose table data).

REF: https://msdn.microsoft.com/es-es/library/ms177570.aspx

Other way is to try to manually reseed the ID counter using DBCC CHECKIDENT

REF: https://msdn.microsoft.com/es-es/library/ms176057.aspx

Autoincrement column value goes higher randomly

2 Answers2