Row compression in SQL Server 2008

Microsoft SQL Server 2008 introduced row compression. This new feature is available in the Enterprise edition only. The row compression feature of SQL Server can compress columns of data to use only the minimum amount of space required.

Row Compression can be enabled when creating a table or index or when altering an index or table.

Compression can be on a row level, page level and backup level. In this article, we illustrate how to create a table with row compression, and alter table, and add compression to it.

If you are looking for backup compression please see 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: 05/27/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 varchar(100), LName varchar(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')
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,30003      ,968 KB,944 KB,8 KB,16 KB

Now let us 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: 05/27/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 varchar(100), LName varchar(100)) with 
(Data_compression = ROW)
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')
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,30003      ,584 KB,576 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].

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: 05/27/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 varchar(100), LName varchar(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')
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 Table,30003      ,968 KB,944 KB,8 KB,16 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 = ROW );

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,30003      ,592 KB,560 KB,8 KB,24 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: 05/27/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 varchar(100), LName varchar(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 Table,30003      ,968 KB,944 KB,8 KB,16 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      ,1256 KB,1096 KB,64 KB,96 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      ,2096 KB,1096 KB,824 KB,176 KB

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

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


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

Now let us add a clustered index on the table with compression using the following transact SQL statement.

create clustered index [NoNCompressed Table4_Cl_Idx] on
[NoNCompressed Table4](ID)
with (data_compression = ROW)

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

EXEC sp_spaceused [NONCompressed Table4]

Result

name,rows,reserved,data,index_size,unused
NoNCompressed Table4,30003      ,744 KB,616 KB,64 KB,64 KB

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

create Nonclustered index [NoNCompressed Table4_NonCl_Idx] on
[NoNCompressed Table4](Fname) 
with (data_compression = ROW)

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

EXEC sp_spaceused [NONCompressed Table4]

Result

name,rows,reserved,data,index_size,unused
NoNCompressed Table4,30003      ,1264 KB,616 KB,496 KB,152 KB

From the space used results of [NONCompressed Table4] and [NONCompressed Table3] you see the row compression on index works very well as well.

Conclusion

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

» See All Articles by Columnist MAK