0

I am setting Identity column with primary key to one column named UserId. So, it will auto increment. It was working fine in my SQL Server 2008 R2. But before sometime, I upgraded it to SQL Server 2012.

It was working fine but don't know what is the wrong, It has suddenly increased number from 13 to 1008.

E.g. last incremented number was 13 so after that It should be 14 but it incremented number as directly 1008 which is very big gap between 13 to 1007.

What should I do in this case? I have not deleted any rows.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • 4
    [Stop making assumptions about `IDENTITY`](https://sqlblog.org/2009/10/12/bad-habits-to-kick-making-assumptions-about-identity), stop worrying about gaps - they are really **NOT** a problem! – marc_s Aug 04 '14 at 04:27
  • @marc_s, then what is the issue ? –  Aug 04 '14 at 04:32
  • 3
    @Jeeten - the issue is that *you* have assumed that identity columns have properties (like having no gaps) that aren't actually guaranteed. If you could put that assumption behind you and just accept the gaps, you'll have a far more pleasant experience with SQL Server database development in the future. – Damien_The_Unbeliever Aug 04 '14 at 06:50

2 Answers2

3

The IDENTITY values are cached for performance reasons.

When SQL Server starts up, it reserves a chunk of values. If SQL Server goes down in the middle of processing, those "reserved" values are lost.

That's what's happening here - it reserves 1000 identity values, and then it was restarted or whatever - so the next identity value is not 1, but roughly 1000 higher than the previous one.

If that's really an issue for you, you can set the Trace Flag T272 on startup to get back the SQL Server 2008 R2 behavior (no caching).

See this CodeProject article explaining this in more detail.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
-1

Unexpected identity increment is good indicator of hidden problems. Let's consider: "ROLLBACK TRANSACTION" always results it's increment.

Try following:

CREATE TABLE table1 (id int identity(1,1), val int)

INSERT INTO table1 (val) VALUES (1)

SELECT * FROM table1 -- one row, id = 1

BEGIN TRANSACTION
INSERT INTO table1 (val) VALUES (2)
INSERT INTO table1 (val) VALUES (3)
INSERT INTO table1 (val) VALUES (4)
INSERT INTO table1 (val) VALUES (5)
INSERT INTO table1 (val) VALUES (6)
INSERT INTO table1 (val) VALUES (7)
INSERT INTO table1 (val) VALUES (8)
INSERT INTO table1 (val) VALUES (9)
ROLLBACK TRANSACTION 

INSERT INTO table1 (val) VALUES (10)

SELECT * FROM table1 -- two rows, id = 10

Review your code for error handling with "ROLLBACK TRANSACTION" (maybe "try/catch" blocks or whatever).

DiMach
  • 1
  • 2
  • 2
    That's a bit wrong. Please see the Remarks session of the [doc](http://msdn.microsoft.com/en-us/library/ms187342.aspx). "Failed statements and transactions can change the current identity for a table and create gaps in the identity column values. The identity value is never rolled back even though the transaction that tried to insert the value into the table is not committed. For example, if an INSERT statement fails because of an IGNORE_DUP_KEY violation, the current identity value for the table is still incremented." It's normal. – Marian Aug 04 '14 at 06:31