Identity Gaps

[cLFlaVA]

Is there a way to set a table up so that, if you have records like this:

[tt]ID BLAH
--------
1 kaht
2 onpnt
3 gmmastros
4 Vragabond[/tt]

where ID is an identity int, and you then deleted a row:

[tt]ID BLAH
--------
2 onpnt
3 gmmastros
4 Vragabond[/tt]

and then inserted another, it would take the missing ID, rather than 5 (the next ID)?

[tt]ID BLAH
--------
1 cLFlaVA
2 onpnt
3 gmmastros
4 Vragabond[/tt]

thanks



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[onpnt]

Not that I know of. There are methods of reseeding the field though. check this blog and Reseeding identities in SQL Server

however I believe it gets hairy in this area and border line effecting the unique value of the identity column. Someone with more experience here probably can state more facts to doing these types of things on an identity column

_{____________ signature below ______________
The worst mistake you'll ever make is to do something simply the way you know how while ignoring the way it should be done}

 

[gmmastros]

There are ways to do this, but it is not recommended.

Suppose there was another table the used the ID to store additional information. Also suppose that you don't have foreign key relationships set up on this table. Without the foreign key contraint, you would be able to delete from the main table without first deleting from the second. Then, you would have orphaned records. If you re-used the identity value, you would then have a child record that doesn't really match.

For example, suppose you had a FavoriteColor table with ID and Color, like this...

[tt][blue]
ID FavoriteColor
----------------
1 Red
2 Blue
3 Green
4 Purple
[/blue][/tt]

If you delete from the main table the ID = 1, but forget to delete the record from the favorite table. Then you add a record in the main table with ID = 1, the person would appear to automatically have a favorite color of red.

However, if you really want to re-use the ID, you should look in to Set IDENTITY_INSERT ON.

Ex:

[tt][blue]
Set Identity_Insert [!]Table_Name[/!] on

[green]-- Get the ID number to use[/green]

Insert into ....

Set Identity_Insert [!]Table_Name[/!] off
[/blue][/tt]

There is nothing that will auto-magically do this for you. Again... I don't recommend it.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cLFlaVA]

Good point gmmastros. I had thought about that briefly, but then thought this table was the lowest level so foreign keys wouldn't matter, but then realized I was wrong.

I'm an R-TARD.

Anyway, thanks to you both - I'll just use the growing ID and cope with the gaps... This table has the potential to grow fast and wanted to avoid having to re-define the ID column in the future... but i think BIGINT should be okay...

do you agree?



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]

 

[kaht]

Why did I get deleted?

-kaht

http://www.silkypups.com

[small](All puppies have now found loving homes, thanks for all who showed interest)[/small]

 

[gmmastros]

First... You can call me George! That's what my friends call me.

There shouldn't be any problems with using a BigInt for your id's. You could also use a UniqueIdentifier.

For more on Unique Identifiers...

http://www.sqlteam.com/item.asp?ItemID=283

I recommend you use BigInt. Unique Identifiers are handy when you have disconnected data that you need to merge later (think laptop with mobile application that needs to sync database changes).

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[cLFlaVA]

kaht, strictly to get a rise out of you

thanks again george.



*cLFlaVA
----------------------------
[tt]"quote goes here"[/tt]
[URL unfurl="true"]http://www.coryarthus.com/[/url]