Click here to Skip to main content
11,705,208 members (50,571 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
Nelek73.9K
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 Sergey Alexandrovich Kryukov 525
1 OriginalGriff 244
2 ppolymorphe 126
3 F-ES Sitecore 110
4 Andy Lanng 85
0 OriginalGriff 8,988
1 Sergey Alexandrovich Kryukov 8,272
2 CPallini 5,189
3 Maciej Los 4,726
4 Mika Wendelius 3,606


Advertise | Privacy | Mobile
Web02 | 2.8.150819.1 | 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