When working with SQL Server, it’s important to understand how delete operations affect the size of tables and indexes. In this article, we will explore the behavior of SQL Server when data is deleted from a table and how it impacts the size of the table and its indexes.

Q: When I delete any data from a table, does SQL Server reduce the size of that table?

A: When data is deleted from a table, SQL Server does not immediately reduce the size of the table. Instead, it marks the pages containing the deleted data as free pages, indicating that they belong to the table. When new data is inserted, SQL Server will use these free pages first. Once these pages are filled up, SQL Server will allocate new pages. If you wait for some time, a background process will deallocate the free pages, ultimately reducing the size of the table.

Q: When I delete any data from a table, does SQL Server reduce the size of the B-Tree or change the level of the B-Tree since there is less data?

A: No, SQL Server behaves differently in this scenario. When data is deleted from a table with a clustered index, SQL Server will deallocate the deleted pages regardless of any hints used. However, without the TABLOCK hint, the deallocation process happens in the background and may not be immediately visible. The number of index levels remains the same, but the size of all levels is reduced, not just the leaf levels.

To illustrate this behavior, let’s consider an example:

USE tempdb
GO

-- Create Table FragTable
CREATE TABLE FragTable (
    ID CHAR(800),
    FirstName CHAR(2000),
    LastName CHAR(3000),
    City CHAR(2200)
)
GO

-- Create Clustered Index
CREATE CLUSTERED INDEX [IX_FragTable_ID] ON FragTable ([ID] ASC) ON [PRIMARY]
GO

-- Insert one Million Records
INSERT INTO FragTable (ID, FirstName, LastName, City)
SELECT TOP 100
    ROW_NUMBER() OVER (ORDER BY a.name) RowID,
    'Bob',
    CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 2 = 1 THEN 'Smith' ELSE 'Brown' END,
    CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 1 THEN 'New York'
         WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 5 THEN 'San Marino'
         WHEN ROW_NUMBER() OVER (ORDER BY a.name) % 10 = 3 THEN 'Los Angeles'
         ELSE 'Houston' END
FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO

-- Check the spaces
EXEC sp_spaceused 'FragTable'
GO

-- Check the percentages
SELECT avg_page_space_used_in_percent, avg_fragmentation_in_percent, index_level, record_count, page_count, fragment_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'), OBJECT_ID('FragTable'), NULL, NULL, 'DETAILED')
GO

-- Delete all from table
DELETE FROM FragTable
GO

-- Check the spaces
EXEC sp_spaceused 'FragTable'
GO

-- Check the percentages
SELECT avg_page_space_used_in_percent, avg_fragmentation_in_percent, index_level, record_count, page_count, fragment_count, avg_record_size_in_bytes
FROM sys.dm_db_index_physical_stats(DB_ID('TempDb'), OBJECT_ID('FragTable'), NULL, NULL, 'DETAILED')
GO

-- Clean up
DROP TABLE FragTable
GO

In the above example, we create a table called FragTable with a clustered index. We then insert one million records into the table. After checking the spaces and percentages, we delete all the data from the table and check the spaces and percentages again.

As you can see from the example, the size of the table is not reduced after deleting the data. Similarly, the levels of the indexes are not resized by SQL Server. However, the leaf level pages where the data is stored are affected. If you wait for some time and run the fragmentation script again, you will notice that the page_count has been reduced to 1.

It’s important to note that the behavior differs for tables with a clustered index and heaps. For heaps, the pages made empty by the delete operation remain allocated to the heap, and the associated space cannot be reused by other objects in the database. However, if you increase the number of inserted rows, you will see that the table size gets reduced. On the other hand, deleting from a table with a clustered index will deallocate the deleted pages, regardless of any hints used. Without the TABLOCK hint, the deallocation happens in the background and may not be immediately visible. Using the TABLOCK hint makes the operation synchronous.

In conclusion, when deleting data from a table in SQL Server, the size of the table and the levels of the indexes are not immediately reduced. However, the space occupied by the deleted data is eventually deallocated, resulting in a reduction in the page count. Understanding this behavior can help you optimize your database and manage its performance effectively.

Do you find this information useful? What is your opinion about this matter?