x

The case of the misbehaving identity

Check out the following script that creates and populates a table

 Drop Table dbo.Region
 GO
 CREATE TABLE dbo.Region(
     RegionId      int             IDENTITY(1,1),
     RegionName    varchar(100)    NOT NULL
 )
 GO
 
 INSERT INTO dbo.Region (RegionName) VALUES  ('Region One'), ('Region Two');
 GO
 
 SELECT * FROM dbo.Region

The output of this what you would expect from a well-behaving Identity field.

 RegionId    RegionName
 ----------- ------------------
 1           Region One
 2           Region Two

Now let's force a couple of values into the Identity column.

 Drop Table dbo.Region
 GO
 CREATE TABLE dbo.Region(
     RegionId      int             IDENTITY(1,1),
     RegionName    varchar(100)    NOT NULL
 )
 GO
 
 SET IDENTITY_INSERT dbo.Region ON;
 INSERT INTO dbo.Region (RegionId, RegionName) VALUES (-9, 'Unknown'), (-99, 'N/A');
 SET IDENTITY_INSERT dbo.Region OFF;
 
 INSERT INTO dbo.Region (RegionName) VALUES  ('Region One'), ('Region Two');
 GO

 SELECT * FROM dbo.Region

The output is

 RegionId    RegionName
 ----------- ------------------
 -9          Unknown
 -99         N/A
 2           Region One
 3           Region Two

Where did RegionId 1 go?

more ▼

asked Jun 16, 2011 at 12:50 PM in Default

avatar image

Raj More
1.8k 82 87 90

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

The answer becomes more clearer if you look at the current value of the identity using

  dbcc checkident('region')

run this after the creation of the table and you get

 Checking identity information: 
       current identity value 'NULL', current column value 'NULL'.
 

meaning, it's currently at 'null' but I'll be incrementing in 1's so the next value will be 1

Then after forcing the insert with set identity_insert on, you will get

 Checking identity information: 
   current identity value '1', current column value '1'.

because you've stated that this identity will start at 1. You've added rows, but haven't gone past 1 with the value for the identity field, so the current value is the same as if you've added one row 'normally', hence the next value is 2.

The reason for that is that the next value is always the value of the greatest value, or seed start value + 1 (or whatever increment you've defined), in other words (max of -99, -9, 1) = 1 + 1 = 2.

In the same way you could add 2 rows 'normally' and then reseed at 100 : (max of 1,2,100) = 100 +1 = 101 would be the next identity value.

more ▼

answered Jun 16, 2011 at 01:15 PM

avatar image

Kev Riley ♦♦
64.2k 48 62 81

It only forces the change from NULL to 1 on your INSERT. However if you have a few rows inserted, and then you force an insert later on, the IDENTITY does not skip (unless you add a higher value).

Jun 16, 2011 at 01:27 PM Raj More
(comments are locked)
10|1200 characters needed characters left

You can use the following to check the current identity seed:

 dbcc checkident('dbo.Region');

Run that right after you create the table and you can see it's NULL--no identity has been seeded. After you perform inserts, though, it defaults to the first value, which is 1 (because you seeded the identity at 1). Do the inserts and after the two records are inserted, run the check statement again and you'll see that up to 3, which is your last inserted value.

I think you're seeing a side effect from the way that identity_insert works: it looks like it guarantees that the current value for an identity isn't NULL after a successful insert. If you pre-seed a region before doing your identity inserts, you'll see that 1 isn't skipped:

 CREATE TABLE dbo.Region(
     RegionId      int             IDENTITY(1,1),
     RegionName    varchar(100)    NOT NULL
 )
 GO
 
 insert into dbo.Region(RegionName) values('Region Zero');
 
 SET IDENTITY_INSERT dbo.Region ON;
 INSERT INTO dbo.Region (RegionId, RegionName) VALUES (-9, 'Unknown'), (-99, 'N/A');
 SET IDENTITY_INSERT dbo.Region OFF;
 
 INSERT INTO dbo.Region (RegionName) VALUES  ('Region One'), ('Region Two');
 dbcc checkident('dbo.Region');
 GO
 
 SELECT * FROM dbo.Region
 
 drop table dbo.Region;
more ▼

answered Jun 16, 2011 at 01:18 PM

avatar image

Kevin Feasel
6.2k 4 7 15

This sounds to me like a bug in the implementation of the IDENTITY. If I did not ask for the IDENTITY to increment, the 1 should not have been skipped.

Jun 16, 2011 at 01:26 PM Raj More

it's not a bug, it's working how it is intended - with no rows the latest value is null, so the next is 1; with some rows the next is determined by those values.

Jun 16, 2011 at 01:28 PM Kev Riley ♦♦
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x749
x393
x35

asked: Jun 16, 2011 at 12:50 PM

Seen: 2575 times

Last Updated: Jun 16, 2011 at 12:59 PM

Copyright 2016 Redgate Software. Privacy Policy