5

In SQL Server 2012, the following query is seeding the identity column myTable_id from 2 instead of 1. Why? myTable_id is also PK.

DELETE FROM myTable;
GO
SELECT * FROM myTable --0 rows are returned as expected
GO
DBCC CHECKIDENT(myTable, RESEED,1)
GO
INSERT INTO myTable(col1,col2,col3) SELECT FROM AnotherTable(col1,col2,col3)
GO
SELECT * FROM myTable --1005 rows are returned as expected, but identity value starts from 2
GO

Remark:

  1. The data inserted is right, the only issue is that the newly inserted data starts from 2 instead of 1.
  2. In the above sql code if I use DBCC CHECKIDENT(myTable, RESEED,0) the identity column correctly starts from 1.
  3. Following is snapshot in SSMS for the myTable_id column:

enter image description here

nam
  • 21,967
  • 37
  • 158
  • 332
  • DBCC CHECKIDENT(myTable, RESEED,0) – mohan111 Aug 10 '17 at 15:56
  • I would imagine because reseeding to 1 means it thinks it already has a value of 1, so the next should be 2. I.e it's zero based in this case, as opposed to 1 based which might be more intuitive given the default behavior of an identity – Xedni Aug 10 '17 at 15:56

4 Answers4

5

From the docs:

The seed value is the value inserted into an identity column for the very first row loaded into the table. All subsequent rows contain the current identity value plus the increment value where current identity value is the last identity value generated for the table or view.

So if you seed from 10, the next value to be inserted will be 11.

DavidG
  • 113,891
  • 12
  • 217
  • 223
  • 1
    It's confusing because if the table had been `truncate`d, it would start from 10, not 11: "If no rows have been inserted into the table since the table was created, or if all rows have been removed by using the TRUNCATE TABLE statement, the first row inserted after you run DBCC CHECKIDENT uses new_reseed_value as the identity." – Blorgbeard Aug 10 '17 at 15:58
  • @DavidG. Yes, but if the current value nothing (as shown in the code above) the next one inserted should be 1, correct? As, the example C in the your document indicates if the current value is 10, the next inserted one would be 11. What I may be misunderstanding here? – nam Aug 10 '17 at 16:03
  • `DBCC CHECKIDENT(myTable, RESEED,1)` sets the "current value" to 1. – Blorgbeard Aug 10 '17 at 16:05
  • @Blorgbeard I agree with your first comment regarding confusion - and that's exactly why I decided to post the question. – nam Aug 10 '17 at 16:06
  • 1
    @nam But you're not using `TRUNCATE` here, so what is the confusion? – DavidG Aug 10 '17 at 16:09
5

There is nothing bad with the answer here but the confusion comes from Microsoft approach itself.

I think that:

DBCC CHECKIDENT(myTable, RESEED, 0)

Should have the same behavior everywhere:

  1. on new created table,
  2. after delete table records,
  3. after truncating the table

Otherwise we need to check the table status before running this.

GuidoG
  • 11,359
  • 6
  • 44
  • 79
gogosweb
  • 101
  • 1
  • 3
3

Works as expected see also
https://learn.microsoft.com/en-us/sql/t-sql/database-console-commands/dbcc-checkident-transact-sql#examples

The value 1 means that the current identity will be at 1 and the next identity will start on 2

To get it starting on 1 you should do 

DBCC CHECKIDENT(myTable, RESEED, 0)
GuidoG
  • 11,359
  • 6
  • 44
  • 79
0

This does the trick for me:

  DBCC CHECKIDENT ([Table], RESEED, 0)
  DBCC CHECKIDENT ([Table], RESEED)
Wouter
  • 2,540
  • 19
  • 31