Available with Production Mapping license.
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 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: row-level compression, unicode compression, and page-level compression.
To learn more about heaps, 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 bis.TableA table of the BISDB database by using row compression, use the following query:
USE [BISDB]
ALTER TABLE [bis].[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)
To learn more 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.
To learn more 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 bis.TableA table of the BISDB database by using page compression, use the following query:
USE [BISDB]
ALTER TABLE [bis].[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)
To learn more 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.
To learn more 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.
To learn more 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 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 bis.TableA table in the BISDB database, execute the following Transact-SQL statement:
USE BISDB;
GO
EXEC sp_estimate_data_compression_savings 'bis', '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 BISDB;
GO
EXEC sp_estimate_data_compression_savings 'bis', 'TableA', NULL, NULL,
'PAGE';
GO
To learn more about how to estimate compression savings, see Estimate Data Compression Savings (Transact-SQL) in the MSDN library.
You can use the SQL script below for the bisdb user tables and indexes and to generate the SQL statements to set page compression for the tables and indexes.
-- Verify BIS Schema Storage
USE [bisdb]
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) = 'bis'
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) = 'bis'
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 BISDB.HYDRO
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) = 'bis'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO
--Index page compression
--Example:
ALTER INDEX R125_pk
ON BISDB.HYDRO
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) = 'bis'
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.
To learn more about TDE, see SQL Server - 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.
To learn more about compression, see SQL Server – Data Compression in the MSDN library.