Click here to Skip to main content
11,647,170 members (69,947 online)
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
Nelek72.3K
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 jyo.net 500
1 F-ES Sitecore 360
2 DamithSL 335
3 OriginalGriff 322
4 CPallini 290
0 OriginalGriff 1,277
1 jyo.net 994
2 DamithSL 946
3 Sergey Alexandrovich Kryukov 853
4 CPallini 760


Advertise | Privacy | Mobile
Web02 | 2.8.150804.2 | Last Updated 2 Nov 2012
Copyright © CodeProject, 1999-2015
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