how did Id get generated in sql server 2008 r2?

Question

I am not sure if this is an issue. I am using simple.data on one of my application. I checked the records in my table. I see the ID from 1, 2,3 ,4,5, jump to 10002, 10003 ... 10041, jump again to 20018 ... 20028.

I noticed that those records that get inserted in the same day, their ID only incremented by 1. if not the same day, will incremented by 10000.

I asked this question on github. get confirmed that this is not a Simple.Data issue. more like sql server problem.

That won't be anything Simple.Data is doing. It sounds like there might be replication set up with IDENTITY ranges assigning new ranges each day, or something. But Simple.Data doesn't try to generate IDs at all, so the problem is elsewhere.

how to check the IDENTITY range?

I checked the Identity specification: Identity increment: 1 Identity seed: 1

btw, ID datatype is BigInt

[Sure you're not on 2012?](http://stackoverflow.com/q/14146148/73226) — Martin Smith, Oct 25 '13 at 18:03
@MartinSmith you are right, I didn't realize I have both 2008 and 2012 installed. please answer my question, so i can give you credit. — qinking126, Oct 25 '13 at 18:11

score 0 · Answer 1 · answered Oct 31 '13 at 09:25

Looks like you have some job (task) which performs the reseed of the identity column of the table by adding 10000 at the end of the day. This can be done by the following script (ex):

if object_id('tempdb..#t') is not null drop table #t
create table #t (id int identity(1,1), val int)

insert into #t select 1
insert into #t select 2
insert into #t select 3

select * from #t

dbcc checkident ('#t')
declare @x bigint
set @x = ident_current('#t')
select @x = floor((@x + 10000)/ 10000) * 10000
dbcc checkident ('#t', reseed, @x)

insert into #t select 1
insert into #t select 2
insert into #t select 3

select * from #t

The results of the above query are shown in the below screenshot:

enter image description here

how did Id get generated in sql server 2008 r2?

1 Answers1