No; data compression only targets what you specify.
Things to be aware of:
- Compression locks the index, unless you have Enterprise Edition and then you can use the ONLINE option.
- The compression process will consume space in the Data file, and it also generates transaction log records as it is a rebuild operation. The space required is only for the index being compressed so work on the index size as a guide for the Data file space being required to compress.
Heap test:
/* create table */
CREATE TABLE test_compression_heap (c1 int IDENTITY (1,1), c2 float);
/* insert some records */
INSERT INTO test_compression_heap (c2) SELECT RAND();
GO 10000
/* create non-clustered index */
CREATE NONCLUSTERED INDEX NCI_c2 ON test_compression_heap(c2);
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* compress heap */
SET STATISTICS IO ON;
ALTER TABLE test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* remove heap compression */
SET STATISTICS IO ON;
ALTER TABLE test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* compress non-clustered index */
SET STATISTICS IO ON;
ALTER INDEX NCI_c2 ON test_compression_heap REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_heap'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* drop test table */
DROP TABLE test_compression_heap;Clustered index test:
/* create table */
CREATE TABLE test_compression_clustered (c1 int IDENTITY (1,1), c2 float);
/* insert some records */
INSERT INTO test_compression_clustered (c2) SELECT RAND();
GO 10000
/* create clustered index */
CREATE CLUSTERED INDEX CI_c1 ON test_compression_clustered(c1);
/* create non-clustered index */
CREATE NONCLUSTERED INDEX NCI_c2 ON test_compression_clustered(c2);
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* compress clustered index */
SET STATISTICS IO ON;
ALTER INDEX CI_c1 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* remove heap compression */
SET STATISTICS IO ON;
ALTER INDEX CI_c1 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = NONE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* compress non-clustered index */
SET STATISTICS IO ON;
ALTER INDEX NCI_c2 ON test_compression_clustered REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE);
SET STATISTICS IO OFF;
/* view compression */
SELECT
OBJECT_NAME(a.object_id) AS table_name
,CASE WHEN a.index_id = 0 THEN 'heap' ELSE b.[name] END AS index_name
,b.type_desc
,a.data_compression_desc
FROM sys.partitions a, sys.indexes b
WHERE 1=1
AND OBJECT_NAME(a.[object_id]) = 'test_compression_clustered'
AND a.[object_id] = b.[object_id]
AND a.index_id = b.index_id
ORDER BY index_name;
/* drop test table */
DROP TABLE test_compression_clustered;Below is a query to help you identify candidates for compressing.
Run in the database you would like to check.
NOTE: sp_estimate_data_compression_savings samples (up to 5000 used pages) the source object and loads this data into an equivalent table (called #sample_tableDBA05385A6FF40F888204D05C7D56D2B) and index created (called sample_indexDBA05385A6FF40F888204D05C7D56D2B) in tempdb. The table or index create in tempdb is then compressed to the requested setting and the estimated compression savings is computed.
SELECT 'EXEC sp_estimate_data_compression_savings @schema_name=''' +b.[name] + ''',@object_name=''' + a.[name] + ''',@index_id=' + CAST(c.index_id AS varchar(10)) +',@partition_number=' + CAST(c.partition_number AS varchar(10)) +',@data_compression=''PAGE'';/*indexname=' + CASE WHEN d.[name] IS NULL THEN 'heap' ELSE d.[name] END + ',size_gb=' + CAST(CAST(((d.dpages * 8) / 1024 / 1024.) AS decimal(20,2)) AS varchar(100)) + '*/'
FROM sys.tables a, sys.schemas b, sys.partitions c, sysindexes d
WHERE 1=1
AND a.[schema_id] = b.[schema_id]
AND a.[object_id] = c.[object_id]
AND a.[object_id] = d.[id]
AND c.[index_id]= d.[indid]
AND ((d.dpages * 8) / 1024 / 1024.) > 1 /* larger than 1GB */
AND c.data_compression_desc = 'NONE'
ORDER BY ((d.dpages * 8) / 1024 / 1024.) DESC;
