autoincrement problem in SQL server

[Brenton]

Hi. I am using an auto increment index in a db in sql server 7. The problem is, that when a record is deleted, it leaves a "hole" in the index count. For example, lets say that there are 10 records and we delete record 7, and we then add a record, the record gets added with index 11 instead of 10. This is due to the
"hole" that was created when we deleted. Now imagine if there are thousands of records and there was considerable deleting going on. There would be "holes" all over the place. This is unwanted. Can anyone help me solve this problem with the index. pls

[davidrobin]

As this is a feature of Autonumber there is little you can do to change it.

There are two possible alternatives
[list=1][*]Change the primary key to a combination of fields where the contents will be unique. This will do away with the autonumber altogether.[*]Have a number field (not autonumber) and store the next key value in a table. When a field is added get the next key from the reference table, use it in your table, and increment the reference field for the next record to be added. When deleting the last record decrement the next key reference value. [/list=1]

While typing the above I realised the problem cannot easily be overcome when deleting records in the middle unless you hold a list of deleted record keys.

The simplest solution is to use point 1.

[Brenton]

Thxs for the reply. I did not expect this to be such a hassle but now that it is, Oh Boy.

if a record is deleted, is there no way to shift the remaining records up one

[davidrobin]

The autonumber is handled by SQL Server so there is no way to 'override' it. Instead of deleting the records you could delete the field's contents and move the remaining fields up one record. Sort of shift them up then finally delete the last now empty record. That really is a lot of unnecessary processing and not really good practice.

When it comes down to it there is no real way to retrieve the record mumber. Even in the example above you still lose the recordnumber of the record you deleted, adding a record means gaps.

[techgnome]

The point of an Identity number is to ensure that each row added will have it's own unique number.... why would you want to overrride it? I'm guessing that's it's probably the pirmary key for that table too.... if so, you could end up with all kinds of probelms with foreign key ralationships......

There is a way, but it's not recommented and it's not a simple solution.

[Brenton]

You see, I am using the field as a primary key, but I am randomly picking records from the table. The problem is that because I am randomly generating the record index to be called up, if there is a "hole" in the index, then it just wastes time, since it must generate a number again and check if it exists.

[Sundance Kid V2]

check www.4guysfromrolla.com (i think). They have a stored proc script that will select random record. So SQL will return the correct record and you don't need to do the if test..

[nightshift]

alternately you could add a field to the database called [Deleted]

when someone executes a delete command, simply set the deleted field to true.

Then when you are grabbing the to search randomly on, pull them into a recordset with an sql like:

"SELECT * FROM [table] WHERE [Deleted] = False"