SQL Server identity specification not sequential

Question

I have noticed that SQL Sever (2008 Express) is randomly jumping forwards when it is inserting auto-incrementing identity specifications.

For example one of my columns "HitStat_ID" will insert sequentially 15, 16, 17, 18, then all of a sudden, it will decide to jump to, say 1012, 1013, 1014.... then 9120, 9121 etc.

It doesn't matter really, so long as they are unique (which they are) but why would it do this? Its doing it in more than one table too. The odd thing is that is only done this over the last week or so, but the application has been in development for months!

I'm working in ASP.NET VB.

This is the insert (in this case):

sql = "INSERT INTO [HitStats] ([PageName], [Date_and_Time], [User_ID]) values ('Home',     
'" + TheDateTime + "', '" + Session("User_ID") + "')"
Dim dbcmd As SqlCommand = New SqlCommand(sql, dbconn)
dbcmd.ExecuteNonQuery()
dbcmd.Dispose()

Please don't go on about SQL injection - its not going to happen!

have you deleted data from your table? If so, then the numbers do not reset unless you truncate the table. — Taryn, Aug 22 '12 at 13:45
@bluefeet - Yes BUT only say 10 rows, nothing like the big gaps in the numbers. — Digital Lightcraft, Aug 22 '12 at 13:49

score 5 · Answer 1 · edited May 23 '17 at 11:44

5

If you're using an application and transactions, when you rollback a transaction you will loose that Auto-incrementing number generated. Also if you make some inserts and rollback them.

See more here

Also, if you delete data, you won't get again that numbers generated, unless you do a reseed. See here

edited May 23 '17 at 11:44

Community

1
1

answered Aug 22 '12 at 13:50

Gonzalo.-

12,512
5
50
82

its an ASP.net web application, using standard SQL to insert single columns, to be honest i have no idea what you mean by a transaction, or a rollback??!! – Digital Lightcraft Aug 22 '12 at 13:55
a transaction let you execute some block of code as an unit, and if you get an error, rollback (cancel) the changes, like if nothing happens (no insert). There are many ways to do that, from c# code, or from sql if you are using stored procedures. Maybe you're doing it in c# without knowing it. See here http://www.c-sharpcorner.com/UploadFile/dchoksi/transaction02132007020042AM/transaction.aspx – Gonzalo.- Aug 22 '12 at 13:58
2

everytime you do a rollback, god kills an autogenerated number. Do it for the numbers – Gonzalo.- Aug 22 '12 at 14:09

score 0 · Answer 2 · answered Aug 22 '12 at 13:52

Such gaps would not be uncommon if the database is being replicated and Automatic Identity Range Management is being used.

What happens is the blocks of identity values are handed to each server involved in replication, so that they can hand out identity values without having to communicate with the other servers. Once a block has been used up on a server, it will "jump" to the next block that has been allocated to it, which will likely be for a much higher range of values.

there is definitely no replication, just one server instance on my dev machine. Good answer though! — Digital Lightcraft, Aug 22 '12 at 13:56

score 0 · Answer 3 · answered Aug 22 '12 at 13:53

0

+1 on @ElVieejo regarding "failed inserts". Another possibility, someone or something might be adding specific values (SET IDENTITY_INSERT ON) -- this might explain the large gaps.

answered Aug 22 '12 at 13:53

Philip Kelley

39,426
11
57
92

nice thought but the sql statement only inserts the 2 other fields, not the identity field. # – Digital Lightcraft Aug 22 '12 at 13:57

Developer Webs · Answer 4 · 2019-05-09T15:32:46.220

0

Identity is not guaranteed to be incremented by 1. See "consecutive values" on Microsoft's page. Also see: Identity increment is jumping in SQL Server database.

edited May 09 '19 at 15:32

answered May 09 '19 at 15:21

Developer Webs

983
9
29

SQL Server identity specification not sequential

4 Answers4

Linked