2

I have an Identity column in a SQL Server table.

When the Identity column reaches the value 100, and then if I insert a new record in table, it would be 101.. But in my table it jumps to 10001, I don't know why this happens?

Plz help

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Gaurav Gupta
  • 491
  • 3
  • 7
  • 20
  • Edit your question to show the `CREATE TABLE...` code for the table in question. Right-click the table in SQL Server Management Studio, then choose "Script Table As" and "CREATE to", then "New Query Window". – Hannah Vernon Nov 08 '13 at 05:58
  • Which **VERSION** of SQL Server are you using? 2005? 2008? 2008 R2? 2012? – marc_s Nov 08 '13 at 06:20
  • Did you have any rolled back transactions on that table? – Szymon Nov 08 '13 at 06:22

1 Answers1

0

This would happen when a bulk insert is done and rolled back. Assuming your column is defined as IDENTITY(1,1), try this code

-- Returns current Identity Value
DBCC CHECKIDENT ('YourTable', NORESEED);

This will return a message

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

Now run the following query

-- Resets the current Identity value to maximum value
DBCC CHECKIDENT ('YourTable', RESEED, 100);

The next insert must pick up from 101

Raj
  • 10,653
  • 2
  • 45
  • 52
  • I am not doing any bulk insert on the table. I insert 425 records in the table, after i insert other records in table, it insert 10385 in the identity coloumn.. – Gaurav Gupta Nov 08 '13 at 04:29
  • The identity numbers cannot go out of whack without any reason. Check the responses to this question http://stackoverflow.com/questions/12074540/sql-server-identity-specification-not-sequential – Raj Nov 08 '13 at 04:52