Removing Deleted Rows?

[GhostWolf]

Environment: SQL Server 2008r2

In my database is a table whose primary field is defined as

  [Seq] [int] IDENTITY(1,1) NOT NULL

Inserting and updating is no problem, deleting I'm beginning to wonder about though.

My problem is that after a row has been deleted, its index, (Seq), isn't removed. For example, beginning with three rows in the table, issue the following commands:

Delete from MyTable where Seq=3
Select * from MyTable  
Insert into MyTable (Col1,Col2,Col3) values('a','b','c')
Select * from MyTable

The result of the first Select, as expected: Seq 3 is gone. The second Select, though, shows that the Seq for the new row is 4, instead of 3.

Is there a way to delete the row, and free up its index?

 

[TonHu]

Check this:

http://msdn.microsoft.com/en-us/library/ms186775.aspx

Be sure to read the section 'Reuse of values'.

 

[jbenson001]

Why is important to keep the seq numbers sequential with no gaps? This should just be an internal number used by code, not shown to anyone.
To avoid the gaps, don't do an actual delete of the row. Have a bit on the table called "active" for example. Set that to 0 for "deleted", then in any sql you can ignore "deleted" rows.
but I not sure if that is what you are looking for.

 

[gmmastros]

As pointed out, the behavior you are seeing is the expected behavior.

In my opinion, the correct use for identity values is for system purposes, not human purposes. In my application, the system user never sees the identity value so they do not care that there may be gaps.

You suggest that this might be a problem for you. Can you please explain why a gap in the identity value is a problem?

-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

 

[GhostWolf]

jbenson001: That sounds like a pretty good work-around.

gmmastros: The sequence number is displayed, and the user is allowed to use it to select a row for editing. While it wouldn't be hard to train each user in re sequence number gaps, it's a bit of tedium I'd hoped to avoid.

Oh well. C'est la vie, or however they say it in Turkey.

Thanks for setting me straight guys.

 

[johnherman]

If you want to control the sequence number, then YOU should control the sequence number, not the database. Create your own table with the sequence number and increment it yourself. And/or create your own table with a list of sequence gap numbers and delete from it as you reuse them.

The most important point is the first. If you want to control a sequence, then control it via YOUR process(es) not the database process(es).

==================================
The trouble with doing something right the first time is that nobody appreciates how difficult it was - Steven Wright

 

[jl3574]

use DBCC CHECKIDENT to reseed after delete this will set your id to where ever you left off.

http://blog.sqlauthority.com/2007/0...ed-table-identity-value-reset-table-identity/

 

[TamarGranor]

Just want to point out that even if you can notice when the last record was deleted, that doesn't address the case when some other record was deleted in the middle of your sequence.

Tamar

 

[Olaf Doschke]

A agree to Tamar, you mostly won't want to have a new record fill an gap created by deleting an old record. You won't always only delete the last row, would you?

If you think of a record number, which automatically adapts to any deletions, that's nothing you put into your table at all, you can generate a numbering on the fly with ROW_NUMBER, see

http://msdn.microsoft.com/de-de/library/ms186734.aspx

This will never be a permanent number, though, and will change by reordering or adding a record sorting towards the middle, etc. You can have that temporarily only. You won't be able to use that as a reference, just temporarily for the current query result, not as a permanent row attribute.

I know situations where a sequential number isn't just generated in the database, but also printed on labels, etc., having a presentation in the physical world. Such data then is getting a natural key, even though an IDENTITY is a widely used surrogate key generator. As it is that, it won't fill gaps, that's not a bug but wanted behavior from IDENTITY.

If you have such needs of gap free sequential numbering, this is not a case for generating them in the database until you have also physically labeled something. That would be data coming from the outside. You'd use something else for generating a primary key, then. At least it's worth thinking about what then really is the situation, where the number of something is given to it uniquely, once only and irreversible. But then you won' have delete operations or put in other words deletes would indicate you create the database record before the essential point in time the real world object should get it's sequence number.

Bye, Olaf.