Click here to Skip to main content
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL SQL-Server
Hi i have record like this
 
sno    name        mail
1       vijay     vijay@email.com
2       ajith     ajith@email.com
3       vivek     vivek@email.com
 
in sno i have applied identity so it will increment by 1 automatically
 
my question is now i delete 2 record means the output is
sno    name        mail
1       vijay     vijay@email.com
3       vivek     vivek@email.com
 
but i need the output like this
sno    name        mail
1       vijay     vijay@email.com
2       vivek     vivek@email.com
the 3 rd record identity field need to change as 2
Posted 1-Nov-12 20:24pm
Edited 1-Nov-12 23:10pm
Nelek47.5K
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

This is by design, IDENTITY columns are maintained by the system and the value does not change when you delete records, this is to maintain referential integrity elsewhere.
 
If you need incrementing values without holes try adding your own column and maintaining the values your self.
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 2

  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

First I have a question. Why do you need to update the identity columns. Identity columns are maintained by SQL Server itself. If you need a report like showing the records in srno without breaking the sequence, you can try below code.
 
Select Row_Number() over ( order by SrNo) as SrNo,Name,Mail from yourTableName
  Permalink  
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 4

Hi Friend,
 
Please see below link....!!!
I am sure it will helpful to you.
http://www.simple-talk.com/sql/t-sql-programming/identity-columns/[^]
 
Regards,
Prasad
  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 235
1 Sergey Alexandrovich Kryukov 144
2 Rob Philpott 100
3 Peter Leow 70
4 CHill60 70
0 OriginalGriff 6,902
1 Sergey Alexandrovich Kryukov 5,623
2 Maciej Los 3,479
3 Peter Leow 3,373
4 DamithSL 2,505


Advertise | Privacy | Mobile
Web03 | 2.8.140721.1 | Last Updated 2 Nov 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