Available with Workflow Manager license.
Using data compression
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.
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 by using row-level compression:
ALTER TABLE tableName REBUILD WITH (DATA_COMPRESSION=ROW)
For example, to rebuild all partitions of the wmx.TableA table of the WMXDB database using row compression, use the following query:
USE [WMXDB]
ALTER TABLE [wmx].[TableA] REBUILD PARTITION = ALL
WITH (DATA_COMPRESSION = ROW)
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 making references to those values from other locations in 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. 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 wmx.TableA table of the WMXDB database using page compression, use the following query:
USE [WMXDB]
ALTER TABLE [wmx].[TableA] REBUILD PARTITION = ALL
WITH
(DATA_COMPRESSION = PAGE)
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 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.
You can configure compression using the preceding Transact-SQL statements or from SQL Server Management Studio using the Data Compression wizard on either tables or indexes. You can use the Data Compression wizard to add and remove compression. To use the Data Compression wizard to change the compression settings for both tables and indexes, perform the following steps:
- In SQL Server Management Studio, right-click the table or index you want to compress, click Storage, and click Manage Compression.
- On the Welcome To The Data Compression Wizard page, click Next.
- On the Select Compression Type page, 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.
- On the Select An Output Option page, choose to create a script, perform the operation immediately, or perform the operation according to a schedule. Click Next, and click Finish to complete the wizard.
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 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 wmx.TableA table in the WMXDB database, execute the following Transact-SQL statement:
USE WMXDB;
GO
EXEC sp_estimate_data_compression_savings 'wmx', '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 WMXDB;
GO
EXEC sp_estimate_data_compression_savings 'wmx', 'TableA', NULL, NULL,
'PAGE';
GO
For more information 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 wmx user tables and indexes and to generate the SQL statements to set page compression for the tables and indexes.
/*---------------------------------------------------------------------
-- Verify WMX Schema Storage
---------------------------------------------------------------------*/
USE [wmxdb]
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) = 'wmx'
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) = 'wmx'
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 WMX.JTX_JOBS_AOI
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) = 'wmx'
AND o.type <> 'S' and o.type = 'U'
ORDER BY o.name,o.type;
GO
/*---------------------------------------------------------------------
--Index page compression
--Example:
/*
ALTER INDEX R125_pk
ON WMX.JTX_JOBS_AOI
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) = 'wmx'
AND i.name NOT LIKE '_WA%'
--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 - 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.
For more information, see SQL Server - Data Compression in the MSDN library.