Geek Answers Handbook

SQL MDF file size does not change

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

Then I inserted 500,000 records into this table, the MDF file size increased to 19456 KB

Then I updated my table and made all the values ​​of one column as Null, but the file size is still the same as ie 19456 KB.

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

I wanted to know why the file size does not change? Does the Null value in a column mean a space?

+4
source share
2 answers

To restore the space needed to compress the database, as this is not done automatically for performance reasons.

Additional Information:

Does column Null have a space?

  • For a variable-width column, NULL does not take up storage space.
  • For a fixed-width column, a NULL value requires the same storage space as any other value.
  • In addition, creating a null column value may in some cases add overhead for each row to store null bitmaps.
+5
source

The MDF file will not automatically shrink after deleting lines unless you include AUTO_SHRINK ( which you don't need !)

Depending on whether NULLs occupy spaces, 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 variables, this will not be.

But even for variable-length columns, simply updating the column value to NULL will likely leave you with internal fragmentation, where free space will be scattered across the data pages.

To see this:

Create Script Table:

 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 selected 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');

Return:

 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

View the first page of data in Internal SQL View

enter image description here

Set Null column

 UPDATE t SET vc=NULL

The previous query shows that 17 pages are still highlighted.

View the first page of data again in the SQL Internal Document Viewer

enter image description here

You can see that the source data still exists and there was no automatic regrouping of rows to return space.

+7
source

More articles:


All Articles