MS SQL Identity Column Not Counting Correctly on Identity Column

Question

I am using MS SQL Server. Will someone please tell me why my identity column in my database just did a crazy jump in numbers when it is supposed to auto increment by 1 automatically? First off I have been entering data in row by row. Eventually after maybe 100 entries it would place them not in order sticking 101 right after 31 not in order. Now all of sudden it has jumped from 290's to 1400's when it should still be in order.... Management tools still show the right number of entries but it just makes no sense how this is supposed to be counting by one. Will someone please explain this to me?

<cfif structKeyExists(form, "user_pass")> 
   <!--- form has been submitted ---> 

   <cffile 
     action       = "upload" 
     fileField    = "filefieldname" 
     destination  = "#expandPath("/webapps/dash/images/")#" 
     nameConflict = "MakeUnique" 
     result       = "myfile" 
   /> 

   <cfset imagePath = myfile.serverDirectory & "/" & myfile.serverFile>
   <cfif isImageFile(imagePath)> 
     <cfquery datasource="test" name="UserRegistration"> 
        INSERT INTO dbo.Users (employee_number, user_name, user_pass, firstname, lastname, position, email, phone_extension, branch, department, status, picture, admin)
 VALUES ( 
    <cfqueryparam value='#form.employee_number#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#form.user_name#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#form.user_pass#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#form.firstname#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#form.lastname#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#form.position#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#form.email#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#form.phone_extension#' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='Desoto' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#form.department#' cfsqltype='cf_sql_varchar' /> 
   , <cfqueryparam value='Active' cfsqltype='cf_sql_varchar' />
   , <cfqueryparam value='#myfile.serverFile#' cfsqltype='cf_sql_varchar' /> 
   , <cfqueryparam value='No' cfsqltype='cf_sql_varchar' />
     )
    </cfquery> 

    <script> 
        self.location="../login.cfm"; 
    </script>
   <cfelse> 
        <script> 
        <!--- not an image file so return some kind of validation message... --->   
            alert("Not an image file"); 
            self.location="../register.cfm"; 
        </script>
   </cfif> 
 </cfif>

Because that is how identity values are defined. They are guaranteed to be monotonically increasing, but not without gaps. — Gordon Linoff, Jan 16 '16 at 00:59
will they eventually fill those numbers? or will they be forever skipped? sorry just seems ridiculous like whats the point of skipping around when i chose to increment by 1 ... — David Brierton, Jan 16 '16 at 01:09
I found it a little surprising too, but not sure why it matters. You do not normally depend on id's being contiguous anyway - only that they are unique. If you need the id of the new record inserted, use cfquery's result attribute. It returns a structure containing the generated id. See the docs on `result.generatedKey` and `result.identityCol`. — Leigh, Jan 16 '16 at 02:56

score 5 · Accepted Answer · answered Jan 16 '16 at 01:15

5

The numbers are guaranteed to be monotonically increasing, but not adjacent. Here is some relevant documentation.

In general, identity columns are also constrained to be unique or primary keys. However, they are not even guaranteed to be unique without these constraints.

They do increase. And the weird behavior is for efficiency, particularly in parallel systems.

It is easy enough to get a sequential value with no gaps, if you need it, at query time:

select row_number() over (order by id) as sequential_with_no_gaps

answered Jan 16 '16 at 01:15

Gordon Linoff

1,242,037
58
646
786

2

*...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.* Huh. I thought the gaps might be due to deletes or identity insert on. Learn something new every day... – Leigh Jan 16 '16 at 02:48
Reading a bit more, it also sounds like [the behavior differs between 2005/2008 and 2012](https://connect.microsoft.com/SQLServer/feedback/details/739013/alwayson-failover-results-in-reseed-of-identity)? – Leigh Jan 16 '16 at 05:31
@Leigh . . . I believe this problem existed in 2008. In any case, the rule is simple: Define `identity` columns to be `unique`. Then, do not expect them to be gapless. They will form a monotonic sequence. If you really want gapless, use a sequence, but that incurs extra overhead, particularly in a high-volume transaction environment. – Gordon Linoff Jan 16 '16 at 13:08
Yes, I do not use identity values in such a way that contiguous values is an issue, but a 1000 id's at a pop seems like a lot to lose for an integer column, if it can happen frequently ... Definitely something I will take consideration from now on when choosing the id data type. – Leigh Jan 17 '16 at 03:39
@Leigh . . . If you search around on the web, you'll find the explanation. I think it has to do with reserving values in a parallel environment during large inserts. There is a good reason for it. – Gordon Linoff Jan 17 '16 at 23:28

MS SQL Identity Column Not Counting Correctly on Identity Column

1 Answers1