DBCC SHRINKFILE can uncompress a compressed table in SQL Server 2008

While preping a demo on data compression for my session for the upcoming SQL Server Day I ran into a funny behaviour of DBCC SHRINKFILE.

In the script below I create a table, fill it with data. The table takes 157 pages.

After compressing it with page compression the table only takes 15 pages. Which is cool.

When I run a DBCC SHRINKFILE the table explotes to a to 116 pages.

I was really confused and tought I hit a bug. But I think I found an explanation. Basically DBCC SHRINKFILE will move used pages at the end of the file to a free spot somewhere else in the data file. The funny thing is that SQL Server is not just moving the pages, but will actually uncompress the pages.

Which leads me to the following conlusion. Although you use ALTER TABLE, INDEX, PARTITION, ... DDL statements to specify compression, compression is not a TABLE, INDEX or PARTITION level property but a page level property.

Or did I hit a bug?

--Use SQL Server Internals Viewer

USE testCompression

GO

ALTER DATABASE testCompression

SET SINGLE_USER WITH ROLLBACK IMMEDIATE

USE MASTER

DROP DATABASE testCompression

GO

CREATE DATABASE testCompression

GO

USE testCompression

CREATE TABLE PageCompressed

(val BIGINT

,st VARCHAR(100)

)

GO

INSERT INTO Pagecompressed

(val, st) SELECT RAND()*1, REPLICATE('A', 100)

GO 10000

SELECT page_count, * FROM sys.dm_db_index_physical_stats (

DB_ID (),

OBJECT_ID ('dbo.Pagecompressed'),

NULL, NULL, 'SAMPLED');

GO

-- Turn on compression

ALTER TABLE Pagecompressed REBUILD

WITH (DATA_COMPRESSION = PAGE);

GO

SELECT page_count, * FROM sys.dm_db_index_physical_stats (

DB_ID (),

OBJECT_ID ('dbo.Pagecompressed'),

NULL, NULL, 'SAMPLED');

GO

DBCC SHRINKFILE(testCompression)

go

SELECT page_count, * FROM sys.dm_db_index_physical_stats (

DB_ID (),

OBJECT_ID ('dbo.Pagecompressed'),

NULL, NULL, 'SAMPLED');

GO