Reuse deleted records

Question

Does deleting a record from a clustered table on a SQL database allow the deleted space to be reused if I insert a new record. If it not what additional steps would be needed to reuse that space.

Answer 1

Deleting records may not get reflected as more free space on Azure Portal on the database but defragment indexes and shrink the database after that may reduce the size of the database. You can test that yourself. Use the following query to see the size of the tables:

select sum(reserved_page_count) * 8.0 / 1024 as "size in MB" from sys.dm_db_partition_stats

Then degrag indexes using below query (during non-business hours if the table is highly used).

DECLARE @TableName varchar(255)
 
 DECLARE TableCursor CURSOR FOR
 (
 SELECT '[' + IST.TABLE_SCHEMA + '].[' + IST.TABLE_NAME + ']' AS [TableName]
 FROM INFORMATION_SCHEMA.TABLES IST
 WHERE IST.TABLE_TYPE = 'BASE TABLE'
 )
 
 OPEN TableCursor
 FETCH NEXT FROM TableCursor INTO @TableName
 WHILE @@FETCH_STATUS = 0
 
 BEGIN
 PRINT('Rebuilding Indexes on ' + @TableName)
 Begin Try
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
 End Try
 Begin Catch
 PRINT('Cannot do rebuild with Online=On option, taking table ' + @TableName+' down for douing rebuild')
 EXEC('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
 End Catch
 FETCH NEXT FROM TableCursor INTO @TableName
 END
 
 CLOSE TableCursor
 DEALLOCATE TableCursor

Run the first query again to see the reduced used space. Reclaimed space can be reused.

Finally, immediately after deleing the rows the space is not immediately available, they can be logically delete (ghost records) that are later remove by a Ghost Cleanup Task.

Answer 2

Yes, the space will be reused, if the new row fits in. Say the clustered index is on the ID column, and you do:

DELETE tbl WHERE ID = 8790
INSERT tbl(ID, col) VALUES (8790, 'Somevalue')

It is likely that the new row will end up on the same page as the old row.

On the other hand, if you do:

DELETE tbl WHERE ID = 3
INSERT tbl (ID, col) (466568790, 'Somevalue')

The new row will most likely not reuse the space of the old row, because the new does not match the old page. The old page may have rows where ID >= 1 and ID <= 97. The new row must go on a page where 466568790 fits into the range for that page.

Answer 3

Hi @Abir Hasan

Here is an answer from this similar thread: Reclaiming space after delete statement in SQL Server.

Whether space is reclaimed? Well, it depends:

For a heap, you end up with the case where SQL server do not reclaim storage after deleting data. Doing a table rebuild would be essential here (or convert the heap to a clustered table).

For an index, it depends on how data is organized compared to what you delete. For example, if you have an index on "OrderDate" and delete the oldest data, then pages will just be deallocated from the beginning of the linked list of pages, and all is fine.

But if you also have an index on LastName for the same table, then deleting data based on old OrderDate will spread out the removed rows all over the place for your LastName index, leaving un-used space in the indexes. Is that bad? Perhaps not, since you later add new data, and that is likely to have a similar spread of LastName as the rows you removed, meaning you won't get page splits.

Best regards,

Cosmog Hong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.