CODEWITHSUNDEEP
sqlsql-serverdatabase
Hasnain Moosani
Hasnain Moosani

Reputation: 13

sql identity issue

I have column id which has identity column

CREATE TABLE EMP
(id int primary key identity(1,1),
name varchar(255));

Problem is that after 10 its gives value 111 then 112 why it is not giving 11

Upvotes: 0

Views: 68

Answers (2)

Bob Tway
Bob Tway

Reputation: 9603

Whatever the cause, you can reset your identity column using the DBCC CHECKIDENT command.

It accepts parameters to allow you to reset the value to whatever you desire. For example this:

DBCC CHECKIDENT ('[TableNameHere]', RESEED, 11)

Will reset the column to 11 - you can substitute whatever number is required as the final parameter.

Using TRUNCATE TABLE will also reset any identity columns - but it will also delete all your data, obviously. Using DELETE will remove data, but it does not change identity values.

Upvotes: 0

krtek
krtek

Reputation: 402

It depends how you are inserting the data into it. If it's simple INSERT INTO and nothing else around it, it's weird.

Maybe it's that issue with MSSQL 2012 server? There is a know bug about identity jump when server restarts.

More information http://www.codeproject.com/Tips/668042/SQL-Server-Auto-Identity-Column-Value-Jump-Is

Upvotes: 2

Related Questions