CODEWITHSUNDEEP
sql-serverdatabase
Digital Lightcraft
Digital Lightcraft

Reputation: 465

SQL Server identity specification not sequential

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!

Upvotes: 5

Views: 3368

Answers (4)

Developer Webs
Developer Webs

Reputation: 1021

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.

Upvotes: 1

Philip Kelley
Philip Kelley

Reputation: 40289

+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.

Upvotes: 0

Damien_The_Unbeliever
Damien_The_Unbeliever

Reputation: 239636

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.

Upvotes: 0

Gonzalo.-
Gonzalo.-

Reputation: 12672

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

Upvotes: 5

Related Questions