Page compression in SQL Server 2008

June 17, 2009

Until SQL Server 7.0 was released, the page size in SQL Server was 2K; now the page size is 8K.

Microsoft introduced page compression in SQL Server 2008. This new feature is available in the Enterprise edition and developer edition. The page compression feature of SQL Server can compress the entire page.

Page compression is applied only when the page gets full. When page compression occurs there are three operations that happen in the following order:

• Row compression
• Prefix compression
• Dictionary compression

If you are looking for only row compression, please see "Row compression in SQL Server 2008" If you are looking for backup compression please check "BACKUP compression in SQL Server 2008."

Let's create a table with no compression option and add some data to it using the following transact SQL Statements:

/****** Object:  Table [dbo].[NoNCompressed Table]    
	Script Date: 06/12/2009 02:24:23 ******/
IF  EXISTS (SELECT * FROM sys.objects 
	WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table]') 
	AND type in (N'U'))
DROP TABLE [dbo].[NoNCompressed Table]
GO
CREATE TABLE [NoNCompressed Table]
(id int, FName char(100), LName char(100))

-- Add 10,000 rows

declare @n int
set @n=0
while @n<=10000
begin
insert into [NoNCompressed Table] values
(1,'Adam','Smith'),(2,'Maria','carter'),
(3,'Walter','zenegger'),(4,'Marianne','smithsonian')
set @n=@n+1
end
GO

Now let's query the space used by this table using the following transact SQL statement.

EXEC sp_spaceused [NONCompressed Table]

Result

name,rows,reserved,data,index_size,unused

NoNCompressed Table,40004,8456 KB,8424 KB,8 KB,24 KB

Let's create a table with compression and add the same amount of data to it using the following transact SQL Statements:

/****** Object:  Table [dbo].[Compressed Table]    
	Script Date: 06/12/2009 02:24:57 ******/
IF  EXISTS (SELECT * FROM sys.objects 
	WHERE object_id = OBJECT_ID(N'[dbo].[Compressed Table]') 
	AND type in (N'U'))
DROP TABLE [dbo].[Compressed Table]
GO
CREATE TABLE [Compressed Table]
(id int, FName char(100), LName char(100)) with 
(Data_compression = PAGE)
declare @n int
set @n=0

-- Add 10,000 rows

while @n<=10000
begin
insert into [Compressed Table] values
(1,'Adam','Smith'),(2,'Maria','carter'),
(3,'Walter','zenegger'),(4,'Marianne','smithsonian')
set @n=@n+1
end
GO

Query the space used by this table using the following transact SQL statement.

EXEC sp_spaceused [Compressed Table]

Result

name,rows,reserved,data,index_size,unused
Compressed Table,40004,	904 KB,896 KB,8 KB,0 KB

The results show that the size of the reserved and data columns are much less in [Compressed table] when compared to [NonCompressed table].

If you want to estimate the size of the compression, you could use the following command to estimate it.

Exec sp_estimate_data_compression_savings 'dbo','NoNCompressed Table',NULL,NULL,'PAGE'

Exec sp_estimate_data_compression_savings 'dbo','NoNCompressed Table',NULL,NULL,'ROW'

When this procedure is executed, the stored procedure samples the data and loads the data in tempdb database. Then in compresses that table in the tempdb to show the estimate.

There are situations where the table with a lot of data already exists and you need to compress the table. In such cases, you could use the ALTER TABLE statement to add the data compression. Let's simulate that scenario by creating another table with no compression. Also, let's add some data similar to the data used above using the following transact SQL statements:

/****** Object:  Table [dbo].[NoNCompressed Table]    Script Date: 06/12/2009 02:24:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table2]') AND type in (N'U'))
DROP TABLE [dbo].[NoNCompressed Table2]
GO
CREATE TABLE [NoNCompressed Table2]
(id int, FName char(100), LName char(100))


declare @n int
set @n=0
while @n<=10000
begin
insert into [NoNCompressed Table2] values
(1,'Adam','Smith'),(2,'Maria','carter'),
(3,'Walter','zenegger'),(4,'Marianne','smithsonian')
set @n=@n+1
end
GO

Query the space used by this table using the following transact SQL statement.

EXEC sp_spaceused [NONCompressed Table2]

Result

name,rows,reserved,data,index_size,unused
NoNCompressed Table2,40004,    	8456 KB,8424 KB,8 KB,24 KB

Add the data compression feature to this table using the ALTER TABLE statement as shown below.

ALTER TABLE [NoNCompressed Table2]
REBUILD WITH (DATA_COMPRESSION = PAGE );

Now let's query the space used by this table using the following transact SQL statement.

EXEC sp_spaceused [NONCompressed Table2]

Result

name,rows,reserved,data,index_size,unused

NoNCompressed Table2,40004,     	592 KB,472 KB,8 KB,112 KB

There are also situations where you need to create an Index using data compression. Let's compare the size of the table with an uncompressed Clustered and Nonclustered index with the size of the table with compressed Clustered and Nonclustered index.

Create a table with no compression and with lot of data using the following transact SQL statement:

/****** Object:  Table [dbo].[NoNCompressed Table3]    Script Date: 06/13/2009 02:24:23 ******/
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[NoNCompressed Table3]') AND type in (N'U'))
DROP TABLE [dbo].[NoNCompressed Table3]
GO
CREATE TABLE [NoNCompressed Table3]
(id int, FName char(100), LName char(100))


declare @n int
set @n=0
while @n<=10000
begin
insert into [NoNCompressed Table3] values
(1,'Adam','Smith'),(2,'Maria','carter'),(3,'Walter','zenegger')
set @n=@n+1
end
GO

Now let's query the space used by this table using the following transact SQL statement.

EXEC sp_spaceused [NONCompressed Table3]

Result

name,rows,reserved,data,index_size,unused

NoNCompressed Table3,30003,    	6472 KB,6408 KB,8 KB,56 KB

Add a clustered index on the table using the following transact SQL statement.

create clustered index [NoNCompressed Table3_Cl_Idx] on
[NoNCompressed Table3](ID)

Query the space used by this table using the following transact SQL statement.

EXEC sp_spaceused [NONCompressed Table3]

Result

name,rows,reserved,data,index_size,unused

NoNCompressed Table3,30003,    	6784 KB,6672 KB,64 KB48 KB

Add a Nonclustered index on the table using the following transact SQL statement.

create Nonclustered index [NoNCompressed Table3_NonCl_Idx] on
[NoNCompressed Table3](Fname) 

Now let's query the space used by this table using the following transact SQL statement.

EXEC sp_spaceused [NONCompressed Table3]

Result

name,rows,reserved,data,index_size,unused
NoNCompressed Table3,30003,10656 KB,	6672 KB,3752 KB,232 KB

Now let us add the compression feature to the above table using the following TSQL.

ALTER TABLE [NoNCompressed Table3]
REBUILD WITH (DATA_COMPRESSION = PAGE );

ALTER INDEX [NoNCompressed Table3_Cl_Idx] on [NoNCompressed Table3]
REBUILD WITH (DATA_COMPRESSION = PAGE );


ALTER INDEX [NoNCompressed Table3_NonCl_Idx] on [NoNCompressed Table3]
REBUILD WITH (DATA_COMPRESSION = PAGE );

Query the space used by this table using the following transact SQL statement.

EXEC sp_spaceused [NONCompressed Table3]

Result

name,rows,reserved,data,index_size,unused
NoNCompressed Table3,30003,    	808 KB,336 KB,320 KB,152 KB

From the space used results of [NONCompressed Table3], before and after altering the table, you could see the page compression on index works very well as well.

Conclusion

This article illustrated how to enable PAGE data compression on a table and index.

» See All Articles by Columnist MAK