CODEWITHSUNDEEP
sqlsql-servert-sqlprimary-keydbcc
Mazdak
Mazdak

Reputation: 781

Does reset primary keys with DBCC CHECKIDENT safe for rows with bigger Id

I have a table with primary integer key value and its auto increment one by one , the values inside were going fine until 116, suddenly after a moment it continues from 10085 value ..

I don't know why but maybe I did a shrink database. But anyway I know about DBCC CHECKIDENT command.

My question is,if I run DBCC CHECKIDENT (mytable, RESEED, 116) , does it harm my rows which have Id from 100085 in the future? I mean when one day my rows reach 100084, will sql server jump from other Ids or it will throw exception to for inserting duplicate Id?

Upvotes: 2

Views: 674

Answers (2)

Martin Smith
Martin Smith

Reputation: 453426

Don't do this

It will throw a duplicate key exception once the identity column comes up to 100084.

It doesn't magically skip previously generated values to avoid clashes. You can easily see this from the below

CREATE TABLE mytable
  (
     x INT IDENTITY PRIMARY KEY,
     Y INT
  )

INSERT INTO mytable
            (Y)
SELECT TOP (200) number
FROM   master..spt_values

DELETE FROM mytable
WHERE  x BETWEEN 1 AND 150;

DBCC CHECKIDENT (mytable, RESEED, 1)

/*Works OK*/
INSERT INTO mytable
            (Y)
SELECT TOP (149) number
FROM   master..spt_values

/*Error*/
INSERT INTO mytable
            (Y)
SELECT TOP (10) number
FROM   master..spt_values

Msg 2627, Level 14, State 1, Line 25 Violation of PRIMARY KEY constraint 'PK__mytable__3BD019E5518439DD'. Cannot insert duplicate key in object 'dbo.mytable'. The duplicate key value is (151). The statement has been terminated.

Upvotes: 2

Yahfoufi
Yahfoufi

Reputation: 2544

it is not recommended to use the following command

DBCC CHECKIDENT (mytable, RESEED, 116)

because it will throw exception when your rows reaches 100084

For more info read the Following Topic . it says that it is recommended that All rows are deleted from the table. if you want to user DBCC CHECKIDENT for reseeding table

Upvotes: 2

Related Questions