SQL MDF file size not changing

Question

In my Db initially i have one table with three columns and no data at that time the MDf file size was 5122 KB.

Then i have inserted 500000 records in this table, MDF file size increased to 19456 KB

Then i have updated my table and made all the values of one column as Null but the file size is still same i.e 19456 KB.

Then i have deleted all the records from this table, but my MDF file size is still 19456 KB.

i wanted to know why the file size is not changing ? Does a Null value in the column takes space ?

Answer 1

The MDF file won't shrink automatically following deletion of rows unless you have AUTO_SHRINK turned on (which you shouldn't!)

As to whether NULLs take up space it depends on the data type. In fixed length columns the full amount of space will still be allocated for the column in rows containing NULL values. For variable ones it won't be.

But even for variable length columns simply updating the column value to NULL would likely leave you with internal fragmentation where the free space is scattered through the data pages.

To see this:

Create Table Script:

CREATE TABLE dbo.t
  (
     id INT IDENTITY PRIMARY KEY,
     vc VARCHAR(4000)
  )

INSERT INTO t
SELECT TOP 26 replicate(char(64 + row_number() OVER( ORDER BY (SELECT 0))), 4000) AS rn
FROM   sys.objects

View allocated pages:

SELECT CONVERT(CHAR(10), object_name(i.object_id)) AS table_name,
       CONVERT(CHAR(16), i.name)                   AS index_name,
       i.index_id,
       CONVERT(CHAR(10), i.type_desc)              AS index_type,
       partition_number                            AS pnum,
       rows,
       CONVERT(CHAR(12), a.type_desc)              AS page_type_desc,
       total_pages                                 AS pages
FROM   sys.indexes i
       JOIN sys.partitions p
         ON i.object_id = p.object_id
            AND i.index_id = p.index_id
       JOIN sys.allocation_units a
         ON p.partition_id = a.container_id
WHERE  i.object_id = object_id('dbo.t'); 

Returns:

table_name index_name       index_id    index_type pnum        rows                 page_type_desc pages
---------- ---------------- ----------- ---------- ----------- -------------------- -------------- --------------------
t          PK__t__7C8480AE  1           CLUSTERED  1           26                   IN_ROW_DATA    17

Viewing the first data page in SQL Internals Viewer

Set the column to Null

UPDATE t SET vc=NULL

The previous query shows that the 17 pages are still allocated

Viewing the first data page again in SQL Internals Viewer

It can be seen that the original data is still there and there was no automatic rearrangement of the rows to reclaim the space.

Answer 2

To reclaim the space you need to shrink the database as this is not done automatically for performance reasons.

More information:

• DBCC SHRINKDATABASE
• DBCC SHRINKFILE
• How do I reclaim space in SQL Server?

Does a Null value in the column takes space ?

• For a variable width column the NULL value takes no storage space.
• For a fixed width column the NULL value requires the same storage space as any other value.
• In addition making a column nullable can in some cases add an overhead per row for storing the nullable bitmap.