Identity Column - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Identity Column

Author

Topic

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-07-24 : 03:56:37

Hai Every one,

I have created a table with EmpId as identity column.Here is the table

create table emp
(
Empid int identity(1,1) primary key,
empname varchar(50)
)
insert into emp values('Suresh')
insert into emp values('Vijay')
insert into emp values('Pradeep')
insert into emp values('RamaKrishna')

select * from emp

delete from emp where empname = 'pradeep'

when I delete the above row,
then the data in the table is
1 Suresh
2 Vijay
4 RamaKrishna
5 Praveen
Now I want the ID should be automaticaly decreased by one 1. like this

1 Suresh
2 Vijay
3 RamaKrishna
4 Praveen

How can we do that.

Anyone help me..
Thanks in Advance.

Suresh Kumar

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-07-24 : 03:57:51

Why do you want to do it this way?

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 03:58:03

why does it matter if there's a gap?

Em

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-24 : 04:55:21

Where do you want to show data?
If you use front end application, do numbering there

Madhivanan

Failing to plan is Planning to fail

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-07-24 : 05:00:59

OK Madhivanan

Is it possible by using cursors or storedprocedures.

Suresh Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-24 : 05:10:16

quote:
Originally posted by soori457

OK Madhivanan

Is it possible by using cursors or storedprocedures.

Suresh Kumar

But you didnt answer my question

Madhivanan

Failing to plan is Planning to fail

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-07-24 : 05:13:53

In front end, I want to show that data in grid view

Suresh Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-24 : 05:21:06

quote:
Originally posted by soori457

In front end, I want to show that data in grid view

Suresh Kumar

When you populate data on grid using the recordset, use a variable that increases for each row and show it as the first column

Madhivanan

Failing to plan is Planning to fail

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-07-24 : 05:25:17

ok
But I'm retrieving that data from back end to grid view,
so, I want all ids in sequential mannaer even if the row is deleted also.

Suresh Kumar

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-07-24 : 05:35:44

Create procedure show_no_gaps
as
Create table #emp(sno int identity(1,1), empname varchar(100))
Insert into #emp(empname)
Select name from emp order by empid
Select sno as empid, empname from #emp

GO

Now use this procedure as source of data

Madhivanan

Failing to plan is Planning to fail

soori457
Yak Posting Veteran

85 Posts

Posted - 2008-07-24 : 05:46:24

Not like tht
there are 10 rows in sequential manner like 1, 2, 3, 4, 5, 6, 7, 8, 9, 10. If I delete 5th row then its displaying
1, 2, 3, 4, 6, 7, 8, 9, 10.
But I want to display like this 1, 2, 3, 4, 5, 6, 7, 8, 9

Suresh Kumar

elancaster
A very urgent SQL Yakette

1208 Posts

Posted - 2008-07-24 : 05:49:49

but WHY? just coz it looks pretty at the frontend? if so, do what Madhi suggested and simply 'present' it that way. The point of the identity column is to keep it unique, which it is

Em

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources