A question that occasionally comes up from people who have made the switch to Microsoft SQL Server from Microsoft Access is "If I delete a bunch of rows, do I need to compact my SQL Server database?"

It's been many years since I've used Access, but I still remember the reason for their concern. Microsoft Access would continually add rows to the end of the table. If some, or even all, of the rows were deleted from the table, Microsoft Access wouldn't reuse the space. It just kept adding rows to what was the very end of the table and never backfilled the "holes". Compacting the database would essentially get rid of the "holes".

Now, I'm not an expert in Microsoft Access and I'm not knocking it. I haven't even used Access since v2.0 back in the 90's. This behavior may have changed. Or perhaps I misremember the way it was back in the day. But sufficed to say with SQL Server, this is not an issue.

But don't take my word for it. Let's consider an example. Let's create a table with three columns and populate it with some test data as shown in the following code.

USE tempdb ;
GO

--create a test table
CREATE TABLE dbo.Test
        (
         col1 INT
        ,col2 VARCHAR(50)
        ,col3 VARCHAR(MAX)
        ) ;

--create some test data
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE 1 = 1
        BEGIN
                SELECT
                        @cnt = @cnt + 1 ;
                INSERT
                        dbo.Test ( col1,col2,col3 )
                VALUES  (
                         @cnt
                        ,'test row # ' + CAST(@cnt AS VARCHAR(100))
                        ,REPLICATE('A',@cnt)
                        ) ;
    
                IF @cnt = 1000 
                        BREAK ;
        END 

Now let's view the table to make sure we have what we think we have.

--view the table 
SELECT
        *
FROM
        dbo.Test ;

Using a Dynamic Management View, let's see how much space our newly created table is consuming.

--check the size of the table 
SELECT
        alloc_unit_type_desc
       ,page_count
       ,avg_page_space_used_in_percent
       ,record_count
FROM
        sys.dm_db_index_physical_stats(
            DB_ID()
            ,OBJECT_ID(N'dbo.Test')
            ,NULL
            ,NULL
            ,'Detailed') ;

On my system, it indicates that 84 data pages are used to store the 1000 rows. Each data page is, on average, 78.4% full.

So, let's delete half of the rows in our Test table and view the results.

--delete the odd rows 
DELETE
        Test
WHERE
        col1 % 2 = 1

--view the table 
SELECT
        *
FROM
        dbo.Test ;

Now let's look at the space consumed by the table by running the DMV query again.

--check the size of the table 
SELECT
        alloc_unit_type_desc
       ,page_count
       ,avg_page_space_used_in_percent
       ,record_count
FROM
        sys.dm_db_index_physical_stats(
            DB_ID()
            ,OBJECT_ID(N'dbo.Test')
            ,NULL
            ,NULL
            ,'Detailed') ;

The number of pages remained constant as expected; the percent used for each page was essentially cut in half, again as expected.

So let's add some new rows to the table to prove that the "holes" will be filled in.

--add some more test data
DECLARE @cnt INT ; 
SET @cnt = 0 ;
WHILE 1 = 1
        BEGIN
                SELECT
                        @cnt = @cnt + 1 ;
                INSERT
                        dbo.Test ( col1,col2,col3 )
                VALUES  (
                         @cnt
                        ,'test row # ' + CAST(@cnt AS VARCHAR(100))
                        ,REPLICATE('B',@cnt)
                        ) ;
    
                IF @cnt = 500 
                        BREAK ;
        END 


--check the size of the table 
SELECT
        alloc_unit_type_desc
       ,page_count
       ,avg_page_space_used_in_percent
       ,record_count
FROM
        sys.dm_db_index_physical_stats(
            DB_ID()
            ,OBJECT_ID(N'dbo.Test')
            ,NULL
            ,NULL
            ,'Detailed') ; 

The results confirm our expectations - the "holes" were re-used by SQL Server. There are still 84 data pages being used by the table and the average percent used for each page has increased to 60.1% from the prior value of 39.4%

Now, since the number of data pages in our example never changed, let's run one more little test prove to ourselves that it can in fact change. Let's add some more rows to the table.

--add yet some more test data
DECLARE @cnt INT ;
SET @cnt = 0 ;
WHILE 1 = 1
        BEGIN
                SELECT
                        @cnt = @cnt + 1 ;
                INSERT
                        dbo.Test ( col1,col2,col3 )
                VALUES  (
                         @cnt
                        ,'test row # ' + CAST(@cnt AS VARCHAR(100))
                        ,REPLICATE('C',@cnt)
                        ) ;
    
                IF @cnt = 1000 
                        BREAK ;
        END 


--check the size of the table 
SELECT
        alloc_unit_type_desc
       ,page_count
       ,avg_page_space_used_in_percent
       ,record_count
FROM
        sys.dm_db_index_physical_stats(
            DB_ID()
            ,OBJECT_ID(N'dbo.Test')
            ,NULL
            ,NULL
            ,'Detailed') ;

Our page count increased to 137.

One final script to clean up after ourselves.

--clean up
DROP TABLE dbo.Test ;

Hopefully this will help allay the concerns that former Access developers have regarding SQL Server's re-use of "holes".

For more information, check out Books Online for sys.dm_db_index_physical_stats to see how we measured the tables consumption of space.

Cheers!

Joe