AutoIncrement Identity Problem

Using SQL Server.

Table1

RecordNo   ID
---------------
2          001
3          002
4         003
....

RecordNo column is the Identity column for Table1

Problem

The RecordNo column sometimes starting from 0, some times starting from 2. How to avoid this problem -- RecordNo Column should always start from 1. It should not start from other numbers.

开发者_StackOverflow中文版

RecordNo   ID
--------------    
1          001
2          002
3          003
....

Need query Help

---

The auto-incremented identity number increments whether the record is ultimately committed or not. It looks to me like possibly the initial insert is failing or is inside a transaction which is not being committed.

The identity column type is meant to be a surrogate identifier, and it's not recommended to use that number for anything else. It is possible to build your own autoincrement functionality, but that's generally a bad idea because of performance and concurrency problems.

Also, it is possible to reseed the identity column back to 1 thusly:

dbcc checkident (table1, reseed, 1)

---

Edit: I assume you have your table definition set properly with Seed set to 1 and increment set to 1 as well.

---

You can set the start value when you create the table:

Create Table Table1
    (
    RecordNo int not null Identity(1,1)
    , Id char(3) ...
    )

IDENTITY [ (seed ,increment ) ]

If you need to reseed a given table then see DBCC CHECKIDENT.