HOW TO SOLVE RESET IDENTITY COLUMN VALUE

Posted by Parthibansk under Sql Server on 12/19/2012 | Points: 10 | Views : 1190 | Status : [Member] | Replies : 4
I GOT A IDENTITY COLUMN NAMED ID..

WHEN I DELETE A RECORD IN TABLE WITH IDENTITY VALUE 3 AND INSERT NEW RECORD THE IDENTITY COLUMN VALUE SKIPS TO 4 INSTEAD OF 3..

I TRIED
DBCC CHECKIDENT('IINFO1',RESEED,3)

BUT STILL GET IDENTITY COL VALUE AS 4..




Responses

Posted by: Ankitsrist on: 12/19/2012 [Member] Starter | Points: 25

Up
0
Down
i also getting same problem

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Nerdanalysis on: 12/19/2012 [Member] Starter | Points: 25

Up
0
Down
DBCC CHECKIDENT('TableName', RESEED, 0)...replace the tablename via table's name............Worked fine for me


Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sandeepmhatre on: 12/19/2012 [Member] Starter | Points: 25

Up
0
Down
you will get your solution here

http://sandeepmhatre.blogspot.com/2012/12/reset-identity-in-sql-server.html

Sandeep M,
Software Developer
Follow me on :
http://sandeepmhatre.blogspot.in

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Sourabh07 on: 12/27/2012 [Member] Starter | Points: 25

Up
0
Down
hi..

if you want to insert the value in order to make the consistency in the unique number ........
eg:

We have the table 'temp' with columns 'id' and 'name'. We are having values in it is


id name
----------
1 'A'
2 'B'
4 'D'

Now, we want to add the value in b/w 2 and 4 i.e. 3, then use the following method to do so...

set identity_insert temp on

insert into temp(id,name) values (3,'C')

set identity_insert temp off

Now, if you see the result, then it could be...

id name
---------
1 'A'
2 'B'
3 'C'
4 'D'

Now onwards, it will behave like an identity column, if you further insert any row in it.


Sourabh07

Parthibansk, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response