Identity column not generating correct IDs

Question

I have a table:

CREATE TABLE [dorkOrder] ( 
    [doId] int identity(1,1)  NOT NULL,
    [shId] int NOT NULL,
    [mopId] int NOT NULL,
    [dorkOrderNo] varchar(20) NULL,
    [doDate] date NULL
);

Now when I am inserting data into it instead of generating doId in this manner 1,2,3,4,5,6,7 it is generating it in following weird manner:

Please help me solve this issue.

Aside from this being a duplicate, most of the value in using an `IDENTITY` column is that you don't - and shouldn't - be worried about the values it's generating. Why are you? — Yuck, Feb 01 '14 at 19:47
@Yuck do you think thats the right way of doing it? what will happen when after reseeding form 1 and entering 1000s records the next again becomes 1002? May be? My question is these are the very first records i am entering so why it is not starring from 1. — Maven, Feb 01 '14 at 19:52
@AaronBertrand Good find and interesting reading. That's most certainly the culprit. Probably going to have a lot more of these as folks move over to 2012... — Yuck, Feb 01 '14 at 19:55

score 5 · Accepted Answer · answered Feb 01 '14 at 19:57

5

This is a known issue in SQL Server 2012. But even without that bug (which may be fixed some day, but may not), you should expect that your IDENTITY column can have gaps. These can happen for a variety of reasons, including deletes and rollbacks.

If you want to implement a sequential column with no gaps, then stop using IDENTITY, build robust serializable processes around inserting new rows, and deny delete / truncate on that table to everyone on earth.

Otherwise, since Microsoft has indicated they have absolutely no intention of offering a "gapless sequential" feature, learn to live with gaps.

answered Feb 01 '14 at 19:57

Aaron Bertrand

272,866
37
466
490

Explains the issue but i think that ouwld just make the `identity` useless as with this long jumps you will hit the maximum int bar very soon. – Maven Feb 01 '14 at 20:10
@Maven You're going to hit 2 billion because you skip 1000 once in a while? I doubt it. Anyway if that is the case, use BIGINT. Good luck exhausting all those values in your lifetime. – Aaron Bertrand Feb 01 '14 at 20:12
once? after evry 19th record i am crossing 1000. – Maven Feb 01 '14 at 20:15
And you're not restarting SQL Server when that happens? Something must be different on your system because I can certainly not reproduce that at all... can you show how you are inserting these 19 rows? – Aaron Bertrand Feb 01 '14 at 20:15
Take note that gapless identity would create major performance problems. BTW, did you maybe changed the default size of connection pool? – pero Feb 01 '14 at 20:22

score 0 · Answer 2 · answered Feb 01 '14 at 20:38

Here is a little test script to show you some interesting things about the identity column

1 - Most people use (1, 1).

However you can increment by five using (1, 5) which will leave gaps.
You can also start at (2000, 1) two thousand if you wanted to.

2 - Deleting all the records from a table will not reset the counter.

3 - Truncating the table will reset the counter to the base.

4 - Gaps can be due to deletes or conditions that prevent an insert.

A sample trigger is at the end to show if big money is not played, gaps will occur.

5 - Last but not least, you can start at the negative range of a type.

For an INT, start with -2147483648 and increment towards 2147483647, gives you twice the numbers. However, if users see the id, you might not want this. Then use a BIGINT.

-- Just playing
use tempdb;
go

-- drop existing
if object_id('superbowl_pool') > 0
drop table superbowl_pool
go


-- create new
create table superbowl_pool
(
id int identity (1,1) primary key,
cash smallmoney
);

-- add data
declare @a int = 0;
while (@a < 100)
begin
  insert into superbowl_pool (cash) values (rand(checksum(newid())) * 50);
  set @a += 1;
end
go

-- show the data
select * from superbowl_pool;

Show the user that deletes do not reset counter.

-- delete does not reset the counter
delete from superbowl_pool;
go

-- Two records
insert into superbowl_pool (cash) values (rand(checksum(newid())) * 50);
insert into superbowl_pool (cash) values (rand(checksum(newid())) * 50);
go

-- Gaps in id column
delete from superbowl_pool where id = 102;
insert into superbowl_pool (cash) values (rand(checksum(newid())) * 50);

-- show the data
select * from superbowl_pool;

Show the user that a truncate resets the counter.

-- truncate table
truncate table superbowl_pool;

-- Two records
insert into superbowl_pool (cash) values (rand(checksum(newid())) * 50);
insert into superbowl_pool (cash) values (rand(checksum(newid())) * 50);
go

-- show the data
select * from superbowl_pool;

The users must play with $20 or more

-- create ins trigger
create trigger dbo.trg_big_money on dbo.superbowl_pool
FOR INSERT
AS
BEGIN

  IF EXISTS (SELECT * FROM inserted where cash < 20)
      ROLLBACK;
END

Identity column not generating correct IDs

2 Answers2