Identity in sql server

Question

0.00/5 (No votes)

See more:

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

Member 8631001

Updated 1-Nov-12 23:10pm

Nelek

v2

Add a Solution

4 solutions

Add a Solution

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

Prasad Guduri · Answer 1 · 2012-11-01T23:36:00

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

Posted 1-Nov-12 23:36pm

Prasad Guduri

Sanjay K. Gupta · Answer 2 · 2012-11-01T23:03:00

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.

SQL

Select Row_Number() over ( order by SrNo) as SrNo,Name,Mail from yourTableName

Shanalal Kasim · Answer 3 · 2012-11-01T22:36:00

Solution 2

Refer:

http://msdn.microsoft.com/en-us/library/ms186775.aspx[^]

Posted 1-Nov-12 22:36pm

Shanalal Kasim

Mehdi Gholam · Answer 4 · 2012-11-01T20:30:00

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.

Posted 1-Nov-12 20:30pm

Mehdi Gholam

Identity in sql server

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Identity in sql server

4 solutions

Solution 4

Solution 3

Solution 2

Solution 1

Add your solution here

Preview 0

Existing Members

...or Join us