CODEWITHSUNDEEP
sqlsql-server
Trojan.ZBOT
Trojan.ZBOT

Reputation: 1488

IDENTITY not generating the seed and increment correctly

I set my column ID as IDENTITY with seed and increment = 1,1. But, it does not start counting from 1. Instead it starts at 2. When I insert the next row, it sets the ID = 7 and not 2. Why could this be happening and how do I fix it ?

I scripted the table and also checked management studio. It is actually an IDENTITY(1,1).

Upvotes: 2

Views: 4284

Answers (5)

Charles Owen
Charles Owen

Reputation: 2910

Actually it's pretty simple to resolve this problem. I am using the Visual Studio 2013 designer, SQL Server Object Explorer. Bring up the offending table in Design View, select the Identify field and pull up the properties. I merely updated the Identity Seed and Increment values and then clicked the Update button. This fixed it for me.

While Identity increments aren't guaranteed, we have come to expect that they should add 1 to highest increment entered thus far.

Upvotes: 0

M.Ali
M.Ali

Reputation: 69594

CREATE TABLE Id_Table (
        [Id] int IDENTITY(1,1), Value INT);
GO

INSERT INTO Id_Table (Value)
VALUES (1), (2), (3), (4), (5), (6), (7), (8), (9), (10);
GO

DELETE FROM Id_Table
WHERE ID = 1 OR  ID = 3 OR ID = 5 OR ID >7
GO

SELECT * FROM Id_Table

DBCC CHECKIDENT('Id_Table', RESEED, 0)  --<-- Reseed to any smallest number
DBCC CHECKIDENT('Id_Table', RESEED)     --<-- Reseed without providing any seed value

SQL SERVER Message

Checking identity information: current identity value '10', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Checking identity information: current identity value '0', current column value '7'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.

Reseed the table to Zero and then just execute the DBCC command without any seed value and it will reseed the Identity value to next highest available Identity value.

Upvotes: 0

Shiva
Shiva

Reputation: 20975

It's by Design. There is NO GUARANTEE of consecutive-ness for IDENTITY Column.

See the response from Microsoft to this "Bug" Report: Identity Column jumps by Seed value.

Posted by Microsoft on 1/25/2013 at 3:38 PM

Thanks for your feedback. This behavior is actually by design as we try to guarantee uniqueness of the ID rather than making sure we don't have gaps. As a result, we do skip some values just in case in certain scenarios so that we don't have any issues around accidentally repeating numbers.

Upvotes: 4

Bill Gregg
Bill Gregg

Reputation: 7147

Even if the table is empty, SQL server will remember the last identity it used. If you want to reset it back to 1 try this:

DBCC CHECKIDENT('Customer', RESEED, 0)

Upvotes: 0

Aaron Bertrand
Aaron Bertrand

Reputation: 280644

IDENTITY does not guarantee a contiguous set of values with no gaps. If this is what you need, you should consider something else (e.g. rolling your own serializable MAX+1 solution). All kinds of things can create gaps:

  • an INSERT that fails
  • a transaction that is rolled back
  • this bug
  • a subsequent DELETE (which can also ruin your own solution too)
  • etc. etc.

Upvotes: 8

Related Questions