Identifier deletion

[Shilohcity]

Hi there

I have a little problem that I am hoping somebody out there can help me with. I have a table with an id column which is set to increment by 1 starting at 1. Each time a record is added it moves to the next number in the sequence but when a record is deleted leaving its id available the next record added still takes the next id in the sequence. What I would like to happen is for any available ids to be used up first e.g.

20 records
record 18 deleted
record added takes id 18 NOT id 21

Does anybody have any suggestions on this?

Thanks
Justin. X-) "Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz

 

[tlbroadbent]

The Identity is designed to function as you describe. You could write code to determine where a gap exists, turn identity insert on, force in the identity to fill the gap, and turn identity insert off. The question is, "Why?" Let SQL Server handle the identity values so you don't have to write additional code and worse yet, bog down your server with unnecessary processing.

If you really want to be in control, don't use identity columns. Create a stored procedure (or function in SQL 2000) to determine the next value and use that. You will have to do all the work necessary to insure that multiple users can't use the same value. Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[ColinM]

There was an article recently about this on

http://www.sqlteam.com

 

[Shilohcity]

Thanks for your comments I guess either way (server or application processing) Im gonna have to write some code.

Justin. "Creativity is the ability to introduce order into the randomness of nature." Eric Hoffer

Visit me at

http://www.dfodesign.co.nz