Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL Server
how to renumber identity column when any row is deleted
Posted 15-Dec-12 4:31am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

DBCC CHECKIDENT ("table1", RESEED, 300)
 

use the above to set the starting index number.
300 will be the starting indexed number when you insert after executing the above query
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 5

AS Griff rigtly pointed out, please try to avoid this scenarion.
 
I you really need this, please use the ReSeed funtinality in SQL Server
 
These links will answer your questions
 
1. Sql-server-reseed-identity-of-table-table-missing-identity-values-gap-in-identity-column]
 
2. Sql-server-delete-truncate-and-reseed-identity
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

Simple rule: Don't.
 
It will cause more problems that it will solve. The problem is that it is intended that the ID should be unique - so when you delete a row, you would have to renumber all the rows with higher numbers. What if that ID is being used in a different table as a foreign key? All of those must be renumbered as well - including any records which are loaded into the memory of other instances of your application, or any other application using the same database. And what if you get two deletes at the same time from different users? What should the new number sequence be?
 
If you want sequential numbers, there are other solutions - but do not try to subvert an identity field as it will cause a lot of intermittent problems in production that may not be apparent in development.
  Permalink  
Comments
Sangunni at 16-Dec-12 0:22am
   
I go with Griff
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

As OrginialGriff don't do this.. Refer MSDN[^]
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

EmpID EmpName
1 Arvind
2 Praveen
3 Sunil
4 Naveen
 
if i delete 2nd record then the preceding record must me renumbered like in below table
 
EmpID EmpName
1 Arvind
2 Sunil
3 Naveen
 
plz give your best answer to maintain identity column
  Permalink  

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
0 OriginalGriff 350
1 Jochen Arndt 190
2 Richard MacCutchan 135
3 Sergey Alexandrovich Kryukov 120
4 DamithSL 105
0 OriginalGriff 6,045
1 DamithSL 4,611
2 Maciej Los 4,087
3 Kornfeld Eliyahu Peter 3,480
4 Sergey Alexandrovich Kryukov 3,310


Advertise | Privacy | Mobile
Web01 | 2.8.141220.1 | Last Updated 16 Dec 2012
Copyright © CodeProject, 1999-2014
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100