ID unusual increment

Question

I created a table in SQL with 3 columns. One of ID (with Identity specification on) and two other columns. I was saving the record in the 2 columns through c# application as ID column gets increment auto. So all was good till 160th record, after that the ID increment went to 1172 and then started increment from there like 1173, 1174 and so on..

I don't know if I did something wrong while creating the table and ID column. Not able to understand. Can someone please guide. I am attaching the screenshot.

Edit: I fixed it by using the query.

DBCC CHECKIDENT (mytable, RESEED, 160)

Now the ID column is back to 161 and incrementing +1. All good.

What version of SQL are you using? It's possible that you accidentally specified `1172` explicitly as the value of the `ID` column, in which case the database might just pick up after that point to continue auto-incrementing. — Tim Biegeleisen, Jul 13 '16 at 05:44
@TimBiegeleisen Using 2013 Express Edition. And no I did not explicitly specified this number. As I said I was inserting the record in the table through C# application. Using insert query and ID was generated auto. It started from 1 and I didn't got any issue in 160 days. a_horse_with_no_name ID was being generated in sequence, started from 1 and after 160 it went to 1172. — Sedate, Jul 13 '16 at 05:54
With SQL Server (at least), auto increment columns are not guaranteed to be sequential. If a large insert was rolled back, for example, the count would not be reset. — Blorgbeard, Jul 13 '16 at 05:54
@Blorgbeard Nothing was rolled back, I deleted or updated a few records. Only few. And I did this deletion before too and got no issue at all, the increment always started +1 from previous entry. — Sedate, Jul 13 '16 at 05:59
If the column stops auto incrementing then you should panic. Just because it jumps is not necessarily cause for alarm. If you want to reset the counter, then read [here](http://stackoverflow.com/questions/510121/reset-autoincrement-in-sql-server-after-delete), but keep in mind that you should only reset to a value which you know is higher than any actual data you still have. — Tim Biegeleisen, Jul 13 '16 at 06:00
Guessing, but I was stumped few years back that if you insert records inside a transaction that gets rolled back, the IDs given out in that tran will get skipped. Makes sense, but a gotcha to me nonetheless. — Jeroen, Jul 13 '16 at 06:03
@TimBiegeleisen Actually the application I created gets and shows the record of last 30 days and 7 days using select statement with conditions of [id] > (SELECT MAX([id]) - 30 and [id] > (SELECT MAX([id]) - 7.. So.. Now last 7 days record is fine as it gets from the date range of 2016-07-07 to 2016-07-12 but last 30 days record is giving results from 2016-07-01 to 2016-07-12.. As it is obvious. Therefore I need to adjust the ID which started unusual increment from 1st July. — Sedate, Jul 13 '16 at 06:12
If you require sequential numbers for that kind of logic you shouldn't be using identity. Identity leaves it up to SQL Server to generate the number. It has never guaranteed no gaps. — Martin Smith, Jul 13 '16 at 06:17
@MartinSmith Ty, i understand that but is there no way I can reset the id to start from 161 after 160 ? — Sedate, Jul 13 '16 at 06:25
Fixed by using this query. DBCC CHECKIDENT (mytable, RESEED, 160) Thanks for guiding guys. — Sedate, Jul 13 '16 at 07:16
@Sedate, this will just keep happening. That is very **error prone** logic to find the last 30 days. I strongly suggest you use `DATEADD` instead. — Nick.Mc, Jul 14 '16 at 00:57

score 0 · Answer 1 · answered Jul 13 '16 at 06:12

Typically, you'll see this if there was INSERTs done in a transaction that got rolled back. Here's a repro for your scenario:

CREATE TABLE #Test (
  Id INT IDENTITY(1, 1) PRIMARY KEY, 
  Txt NVARCHAR(100)
);

INSERT INTO #Test (Txt) VALUES ('A'), ('B');

BEGIN TRAN mytran
INSERT INTO #Test (Txt) VALUES ('C'), ('D');
ROLLBACK TRAN mytran

INSERT INTO #Test (Txt) VALUES ('E'), ('F');

SELECT * FROM #Test;

DROP TABLE #Test;

This will output:

Id  Txt
1   A
2   B
5   E
6   F

So with 'C' and 'D' missing, and a gap in the Id field between 2 and 5.

ID unusual increment

1 Answers1