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

Get the Free Newsletter!

Subscribe to Cloud Insider for top news, trends & analysis

Latest Articles