SQL Server: arbitrary auto-increment of primary key -

this question has answer here:

• identity increment jumping in sql server database 6 answers

we're running sql server 2012 sp1 x64 (11.0.3000.0)

i have following table invoiceid field auto-incrementing, primary key:

create table orders(     invoiceid           bigint           identity(1001,1) not replication,     orderid             varchar(8)       not null,     ...  -- other fields removed brevity     constraint [pk_orders] primary key clustered (invoiceid)     on [primary],  ) 

new rows inserted though simple stored procedure following:

set  xact_abort on set  nocount on  begin transaction     insert orders(           orderid,           ... -- other fields removed brevity         )     values  (           @orderid,           ...         )                    select @newrowid = scope_identity() commit transaction 

the above sproc returns newly created row-id (orders.invoiceid) caller.

the code working perfectly, [invoiceid] starting 1001 , incrementing 1 each successive inserts.

our users inserted 130 rows. [invoiceid] @ 1130, on next insert value jumped 11091!

here's data screenshot:

i'm baffled happened here. why did auto-inc counter skip 10,000 points?

we're using value of [invoiceid] generate barcodes, we'd prefer value remain in specific range, preferably in contiguous series.

i've perused t-sql documentation failed find related issue. normal behavior (arbitrary population) of identity field?

update marting & aron, i've found work-around. here's official response microsoft:

in sql server 2012 implementation of identity property has been changed accommodate investments other features. in previous versions of sql server tracking of identity generation relied on transaction log records each identity value generated. in sql server 2012 generate identity values in batches , log max value of batch. reduces amount , frequency of information written transaction log improving insert scalability.

if require same identity generation semantics previous versions of sql server there 2 options available:

• use trace flag 272 o cause log record generated each generated identity value. performance of identity generation may impacted turning on trace flag.

• use sequence generator no cache setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx) o cause log record generated each generated sequence value. note performance of sequence value generation may impacted using no cache.

example:

create sequence s1 int start 1 no cache;  create table t1 (id int primary key default next value s1, col int not null); 

update marting & aron, i've found work-around. here's official response microsoft:

in sql server 2012 implementation of identity property has been changed accommodate investments other features. in previous versions of sql server tracking of identity generation relied on transaction log records each identity value generated. in sql server 2012 generate identity values in batches , log max value of batch. reduces amount , frequency of information written transaction log improving insert scalability.

if require same identity generation semantics previous versions of sql server there 2 options available:

• use trace flag 272 o cause log record generated each generated identity value. performance of identity generation may impacted turning on trace flag.

• use sequence generator no cache setting(http://msdn.microsoft.com/en-us/library/ff878091.aspx) o cause log record generated each generated sequence value. note performance of sequence value generation may impacted using no cache.

example:

create sequence s1 int start 1 no cache;  create table t1 (id int primary key default next value s1, col int not null);