CODEWITHSUNDEEP
sqlsql-server-2012
Ten98
Ten98

Reputation: 832

SQL Server 2012 - Converting ROW compression to PAGE compression

I have several very large, static databases with ROW compression on the tables.

I would like to convert these to PAGE compression, because the data will be reduced in size considerably if I do that.

As I understand it, when applying PAGE compression, SQL server first applies ROW compression then applies PAGE compression.

If I tell it to rebuild a partition with PAGE compression which is already compressed with ROW compression, will it simply apply PAGE compression to the table right away, or will it start from scratch and re-apply ROW compression before it applies PAGE compression?

Upvotes: 0

Views: 328

Answers (1)

Stoleg
Stoleg

Reputation: 9300

My opinion on this would be that PAGE or ROW compression happens "from scratch", rather than reusing in compression related information or already compressed data. here is the reason.

We know that: - We use REBUILD INDEX statement to apply or remove compression - Each partition has its own compression property set to either NONE, ROW< PAGE.

Also a quote from MSDN article Reorganize and Rebuild Indexes:

Rebuilding an index drops and re-creates the index. This removes fragmentation, reclaims disk space by compacting the pages based on the specified or existing fill factor setting, and reorders the index rows in contiguous pages.

Rebuilding an index is the actual operation SQL Server does. Compression is a setting for that operation. From the quote we see that dropping an index deletes any existing information. So I think it answers your question.

Upvotes: 1

Related Questions