CODEWITHSUNDEEP
sql-servercompression
Sandy Gettings
Sandy Gettings

Reputation: 713

Does SQL compression of "None" clean up a data table?

I'm experimenting with reducing the storage for a SQL Server database that's over 4 GB and growing. I opened the Data Compression Wizard in SSMS, selected compression type of "None" on the largest table, and clicked the Calculate button. I was surprised that the result was less than half the original (2.2 GB down to less than 1 GB), even though there is no compression.

Is this a real savings? That is, is setting compression to "none" performing a one-time clean-up of unused space? Or is this a waste of time?

Compression Wizard

Upvotes: 3

Views: 1063

Answers (2)

Sandy Gettings
Sandy Gettings

Reputation: 713

Ok, after testing with a sample database, here's what I found. The important thing is that setting a table's compression to "None" causes SSMS to generate a script like the following:

USE [MyDatabase]
ALTER TABLE [dbo].[MyTable] REBUILD
PARTITION = ALL
WITH (DATA_COMPRESSION = NONE)

In particular, note the "alter table MyTable rebuild." If the table has been modified, such as dropping columns, there is unused space in each row. The REBUILD option reclaims this space, and the table gets smaller.

So, to answer my own question: Yes, setting compression to "None" is a one-time clean-up of unused space within the table. The resulting savings were not as good as the "Calculate" button would suggest, but still significant in my example.

All that said, a simple "alter table MyTable rebuild" would accomplish the same thing.

(My thanks to those who posted!)

Upvotes: 2

Igor Micev
Igor Micev

Reputation: 1652

No, it doesn't.

Compression Type = NONE simply means rebuilding of the indexes in the table without applying any compression. So, it won't change the Figure. Next time you open the Compression wizard for the table, it will show the same numbers after clicking on "Calculate".

https://msdn.microsoft.com/en-us/library/ms190273.aspx

https://msdn.microsoft.com/en-us/library/ms188388.aspx

Upvotes: 1

Related Questions