SQL Server Performance Forum – Threads Archive
Duplicate Rows
HiI am able to find duplicate rows in my table and I want to get rid of that but the thing is the table has an IDENTITY row as a PK, if i am deleting few rows how does the IDENTITY number reseed? Thanks!
"He laughs best who laughs last"
It doesn’t reseed by itself, you have to execute the DBCC CHECKIDENT command – check BOL for the exact syntax.
And you dont have to renumber it. It is not needed actually Madhivanan Failing to plan is Planning to fail
do I need to execute DBCC CHECKIDENT for the row where i deleted so that it has continuation number.
say I deleted row 8, how can i get number 8 for row 9. Thanks!
"He laughs best who laughs last"
Thats what referred above to run DBCC CHECKIDENT against the table to get the correct values. Satya SKJ
Microsoft SQL Server MVP
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
You can’t "renumber" the identity column on existing records. There’s really no need to. Also, if you are using this as a FK on a detail record, you want to keep the existing values. If you’re looking for a numbering system that never leaves a gap, you have to look at other methods – like adding the number after the record is committed.
http://searchsqlserver.techtarget.com/generic/1,295582,sid87_gci1170305_tax301329,00.html?track=NL-464&ad=543997&adg=301324&bucket=REF The utilities sp_OrganizeKeyValues or sp_ResetNextKeyValue in this article should be helpful to perform the operation you have requested for.
uks
]]>