Page compression in SQL Server 2008

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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles