SQL server ID problem 1

[mayamanako]

Hi guys,

If I have an original table like this, (where id is unique and not replicated)

id itemhere
1 apple
2 grapes
3 banana
4 strawberry
5 kiwi

and deleted some rows like this:

id itemhere
1 apple
2 grapes



and added new entries, how do I make it to look like this:

id itemhere
1 apple
2 grapes
3 mango
4 blackberry
5 peach

because when I deleted some items, the newly-added items' ids are not anymore 3,4 and 5 but 6,7,8? how do I make it to have 3,4 and 5 still? Is it possible?

Thanks for any sugestions.

 

[gmmastros]

After you delete your rows, you can run this code:

dbcc checkident('[!]YourTableNameHere[/!]',RESEED, 0)
dbcc checkident('[!]YourTableNameHere[/!]',RESEED)

I have to mention that this is probably not such a good idea. This will do exactly what you asked in your question, but there may be broader implications here.

Imagine you have the 5 original rows, and then you delete a row in the middle. If you reseed your table with the code I show above, it will not fill in the gap, but will continue on.

To see what I mean, run this code:

Create Table #Test(id int identity(1,1) Primary Key, ItemHere VarChar(20))

Insert Into #Test(ItemHere) Values('apple')
Insert Into #Test(ItemHere) Values('grapes')
Insert Into #Test(ItemHere) Values('banana')
Insert Into #Test(ItemHere) Values('strawberry')
Insert Into #Test(ItemHere) Values('kiwi')

Select * From #Test

Delete From #Test Where ItemHere In ('banana')

Select * From #Test

dbcc checkident('#Test',RESEED, 0)
dbcc checkident('#Test',RESEED)

Insert Into #Test(ItemHere) Values('Watermelon')

Select * From #Test

Drop Table #Test

The output is:

After adding the original 5 rows.

id          ItemHere
----------- --------------------
1           apple
2           grapes
3           banana
4           strawberry
5           kiwi

After deleting row in the middle

id          ItemHere
----------- --------------------
1           apple
2           grapes
4           strawberry
5           kiwi

After adding another row.

id          ItemHere
----------- --------------------
1           apple
2           grapes
4           strawberry
5           kiwi
6           Watermelon

The other (and possibly bigger) problem here concerns child records. If you have other tables in your database that stores the ID from this table, then you could be opening yourself up to bigger problems.

For example, suppose you had a people table with a "FavoriteFruit" column. Orignally the id = 3 is stored for person "George" (because bananas are my favorite). Then you delete the last 3 rows, reseed that table, and then add another row for watermelon. The FavoriteFruit id in the person table is still 3, but now it's pointing to watermelon instead of banana.

Basically, what I'm saying is... if you plan on reseeding your table, then you MUST be very careful with those id's.


-George
Microsoft SQL Server MVP
My Blogs
SQLCop
twitter
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[mayamanako]

Hi George,

Thanks very much for your help. I have taken on board everything you said and suggested.

Have a nice day!

 

[DaveInIowa]

Would it help your project if you included a pseudo-id number in your SELECT statement such as

SELECT id, [red]ROW_NUMBER() OVER (ORDER BY id) as contiguousId,[/red] itemhere ...