identity sequence issue - 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

SQL Server 2005 Forums

Transact-SQL (2005)

identity sequence issue

Author

Topic

VipinMitta
Starting Member

12 Posts

Posted - 2010-03-10 : 08:12:25

Hi experts,

i create one table with idendity column as below

create table table_master(table_id int identity(1,1), table_name varchar(100))

insert into table_master
values('a')

insert into table_master
values('b')

insert into table_master
values('C')

the o/p will be as below

1,'a'
2,'b'
3,'c'

now we excute delete statement as below

delete from table_master where table_name = c

then insert new row

insert into table_master
values('d')

select * from table_master

1,'a'
2,'b'
4,'d'

but i want value in a sequence even i deleted the records

is there having any solution to use identity and get the value sequence even i delete the records

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2010-03-10 : 08:28:55

why would you want to do this.

If you had your a, b, c records in order and you deleted b would you want c to get a sequence of 2? Storing a precomputed column for sequence will involve a lot of updating when you delete records from the middle.

In 2005 you can always use ROW_NUMBER() to return a sequence in a query. For your example the SELECT statement would be


SELECT
    ROW_NUMBER() OVER(ORDER BY [table_Id]) AS [sequence]
    , *
FROM
    table_master

I think it would be better just to generate the sequence on demand when required rather than trying to store it permanently.
Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2010-03-10 : 12:48:48

and always you can retrieve the records in correct order even if they're not contiguos

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources