Using data compression for the Production Mapping workspace in SQL Server—Help

Available with Production Mapping license.

Row-level compression
Unicode compression
Page-level compression
Estimating compression
Compression and TDE

Row and page compression for tables and indexes enables you to save storage space by reducing the size of the database. Data compression has the drawback of increasing CPU usage because the data must be compressed and decompressed when being accessed. You cannot use data compression with system tables, and only the Enterprise and Developer editions of SQL Server 2012 support data compression.

You can configure data compression on the following:

Clustered tables
Heap tables (a heap is a table without a clustered index)
Nonclustered indexes
Indexed views
Individual partitions of a partitioned table or index

There are three forms of data compression you can use with SQL Server 2012: row-level compression, unicode compression, and page-level compression.

For more information, see Heaps (Tables without Clustered Indexes) in the MSDN library.

Row-level compression

Row-level compression works by using more efficient storage formats for fixed-length data.

Row-level compression uses the following strategies to save space:

Storing fixed-length numeric data types and CHAR data types as though they were variable-length data types
Not storing NULL or 0 values
Reducing metadata required to store data

Although it does reduce the amount of space that data uses, row-level compression does not provide the storage improvements of page-level compression. The advantage of row-level compression is that it requires less CPU usage than page-level compression. You use the following syntax to compress a table with row-level compression:

ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)

For example, to rebuild all partitions of the pm.TableA table of the PMDB database by using row compression, use the following query:

USE [PMDB]
ALTER TABLE [pm].[TableA] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)

You use the following syntax to configure an index with row-level compression:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=ROW)

For more information about row-level compression, see Row Compression Implementation in the MSDN library.

Unicode compression

Unicode compression enables the database engine to compress unicode values stored in page or row compressed objects. You can use unicode compression with the fixed-length nchar(n) and nvarchar(n) data types. Unicode compression is automatically used where appropriate when you enable row and page compression.

For more information about unicode compression, see Unicode Compression Implementation in the MSDN library.

Page-level compression

Page-level compression compresses data by storing repeating values and common prefixes only once and then making references to those values from other locations within the table. When page compression is applied to a table, row compression techniques are also applied. Page-level compression uses the following strategies:

Row-level compression is applied to maximize the number of rows stored on a page.
Column prefix compression is applied by replacing repeating data patterns with references. This data is stored in the page header.
Dictionary compression scans for repeating values and stores this information in the page header.

The benefits of page compression depend on the type of data compressed. Data that involves many repeating values will be more compressed than data populated by more unique values. You use the following general syntax to apply page-level compression:

ALTER TABLE name REBUILD WITH (DATA_COMPRESSION=PAGE)

For example, to rebuild all partitions of the pm.TableA table of the PMDB database by using page compression, use the following query:

USE [PMDB]
ALTER TABLE [pm].[TableA] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE)

You use the following syntax to configure an index with page-level compression:

ALTER INDEX indexName ON tableName REBUILD PARTITION ALL WITH (DATA_COMPRESSION=PAGE)

For more information about page-level compression, see Page Compression Implementation in the MSDN library.

If tables or indexes are partitioned, you can configure compression on a per-partition basis. If you split a partition by using the ALTER PARTITION statement, the new partitions inherit the data compression attribute of the original partition. If you merge two partitions, the resulting partition has the compression attribute of the destination partition. Although compression does allow more rows to be stored on a page, it doesn’t alter the maximum row size of a table or index. You can’t enable a table for compression if the maximum row size and the compression overhead exceed 8,060 bytes. The default compression setting for indexes is NONE, and you must specify the compression property for indexes when you create them. Nonclustered indexes do not inherit the compression property of the table, but clustered indexes created on a heap inherit the compression state of the heap. Data compression applies only at the source, so when you export data from a compressed source, SQL Server will output the data in uncompressed row format. Importing uncompressed data into a target table enabled for compression will compress the data.

For more information about data compression, see Data Compression in the MSDN library.

To change the compression settings for both tables and indexes using the Data Compression wizard, perform the following steps:

Start SQL Server Management Studio.
Right-click the table or index you want to compress, click Storage, and click Manage Compression.

The Welcome To The Data Compression Wizard dialog box appears.
Click Next.

The Select Compression Type dialog box appears.
Choose the type of compression.

You can choose to use the same compression type for all partitions or choose among row, page, and none on a per-partition basis.
Click Calculate to determine the difference between current space usage and compressed usage.

The Select An Output Option dialog box appears.
Choose whether to create a script, perform the operation immediately, or perform the option according to a schedule.
Click Next.
Click Finish.

For more information about the Data Compression wizard, see Data Compression Wizard Help in the MSDN library.

Estimating compression

The best way to determine the benefits of compression on an object is to use the sp_estimate_data_compression_savings stored procedure. The benefits of compression depend on factors such as the uniqueness of data. The sp_estimate_data_compression_savings stored procedure is available in the Enterprise edition of SQL Server 2012 only.

The syntax of the stored procedure is as follows:

sp_estimate_data_compression_savings[ @schema_name = ] 'schema_name', [ @object_name = ]
'object_name', [@index_id = ] index_id,[@partition_number = ] partition_number,
[@data_compression = ] 'data_compression'

For example, to configure an estimate of the compression benefits of using row compression on the pm.TableA table in the PMDB database, execute the following Transact-SQL statement:

USE PMDB;
GO
EXEC sp_estimate_data_compression_savings 'pm', 'TableA', NULL, NULL,
'ROW';
GO

To configure an estimate of the compression benefits of using page compression on the same table, execute the following Transact-SQL statement:

USE PMDB;
GO
EXEC sp_estimate_data_compression_savings 'pm', 'TableA', NULL, NULL,
'PAGE';
GO

For more information about how to estimate compression savings, see sp_estimate_data_compression_savings (Transact-SQL) in the MSDN library.

You can use the SQL script below for the pm user table and indexes and to generate the SQL statements to set page compression for the tables and indexes.

/*---------------------------------------------------------------------
-- Verify PM Schema Storage
---------------------------------------------------------------------*/
USE [pmdb]
GO
/*---------------------------------------------------------------------
--List all tables
---------------------------------------------------------------------*/
SELECT USER_NAME(o.uid) [owner], o.name,o.id,o.type,o.status
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'pm'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO
/*---------------------------------------------------------------------
--List all indexes
---------------------------------------------------------------------*/
SELECT USER_NAME(o.uid) [owner], OBJECT_NAME(i.id) [table], i.name [index],o.type [type]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'pm'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO

/*---------------------------------------------------------------------
--Table page compression
--Example:
/*
ALTER TABLE PM.CITIES 
REBUILD WITH (DATA_COMPRESSION = PAGE);
GO
*/
---------------------------------------------------------------------*/
--Generate script to set table page compression:
SELECT 'ALTER TABLE ' + USER_NAME(o.uid) + '.' + o.name + ' REBUILD WITH (DATA_COMPRESSION = PAGE);' [TXTSQL]
FROM sysobjects o
WHERE USER_NAME(o.uid) = 'pm'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO

/*---------------------------------------------------------------------
--Index page compression
--Example:
/*
ALTER INDEX R125_pk 
ON PM.CITIES
REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;
GO
*/
---------------------------------------------------------------------*/
--Generate script to set index page compression:
SELECT 'ALTER INDEX ' + i.name + ' ON ' + USER_NAME(o.uid) + '.' + OBJECT_NAME(i.id) +
       ' REBUILD WITH ( DATA_COMPRESSION = PAGE );' [TXTSQL]
FROM sysindexes i inner join sysobjects o ON i.id = o.id
WHERE USER_NAME(o.uid) = 'pm'
AND o.type <> 'S' and o.type = 'U' and i.indid = 1
ORDER BY USER_NAME(o.uid),OBJECT_NAME(i.id),i.name;
GO

Compression and TDE

Encryption of the database file is performed at the page level. The pages in an encrypted database are encrypted before they are written to disk and decrypted when read into memory. TDE does not increase the size of the encrypted database.

For more information, see SQL Server 2012 - Transparent Data Encryption (TDE) in the MSDN library.

Encrypted data compresses significantly less than equivalent unencrypted data. If TDE is used to encrypt a database, backup compression will not be able to significantly compress the backup storage. Therefore, using TDE and backup compression together is not recommended.

For more information, see SQL Server 2012 – Data Compression in the MSDN library.