Creating Compressed Tables and Indexes

Creating Compressed Tables and Indexes

SQL Server 2008 supports both row and page compression for both tables and indexes. Data compression can be configured for the following database objects:

·    A whole table that is stored as a heap.

·   A whole table that is stored as a clustered index.

·    A whole nonclustered index.

·     A whole indexed view.

·     For partitioned tables and indexes,the compression option can be configured for each partition, and the various partitions of an object do not have to have the same compression setting.

The compression setting of a table is not automatically applied to its nonclustered indexes. Each index must be set individually. Compression is not available for system tables. Tables and indexes can be compressed when they are created by using the CREATE TABLE and CREATE INDEX statements. To change the compression state of a table, index, or partition, use the ALTER TABLE or ALTER INDEX statements.

Note
 
If the existing data is fragmented, you might be able to reduce the size of the index by rebuilding the index instead of using compression. The fill factor of an index will be applied during an index rebuild, which could potentially increase the size of the index. For more information, see Fill Factor.
 

Examples

Some of the following examples use partitioned tables and require a database that has filegroups. To create a database that has filegroups, execute the following statement.

CREATE DATABASE TestDatabase

ON  PRIMARY

         ( NAME = TestDatabase, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDB.mdf'),

FILEGROUP test1fg

        ( NAME = TestDBFile1, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDBFile1.mdf'),

FILEGROUP test2fg

       ( NAME = TestDBFile2, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDBFile2.ndf'),

FILEGROUP test3fg

       ( NAME = TestDBFile3, FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDBFile3.ndf'),

FILEGROUP test4fg

      ( NAME = TestDBFile4,FILENAME = 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\TestDBFile4.ndf') ;

GO

To switch to the new database:

USE TestDatabase

GO

A. Creating a table that uses row compression

The following example creates a table and sets the compression to ROW.

CREATE TABLE T1 (c1 int, c2 nvarchar(50) ) WITH (DATA_COMPRESSION = ROW);

B. Creating a table that uses page compression

The following example create a table and sets the compression to PAGE.

CREATE TABLE T2 (c1 int, c2 nvarchar(50) ) WITH (DATA_COMPRESSION = PAGE);

C. Setting the DATA_COMPRESSION option on a partitioned table

The following example uses the TestDatabase table that is created by using the code provided earlier in this section. The example creates a partition function and scheme, and then creates a partitioned table and specifies the compression options for the partitions of the table. In this example, partition 1 is configured for ROW compression, and the remaining partitions are configured for PAGE compression.

To create a partition function:

CREATE PARTITION FUNCTION myRangePF1 (int) AS RANGE LEFT FOR VALUES (1, 100, 1000) ;

To create a partition scheme:

CREATE PARTITION SCHEME myRangePS1 AS PARTITION myRangePF1 TO (test1fg, test2fg, test3fg, test4fg) ;

GO

To create a partitioned table that has compressed partitions:

CREATE TABLE PartitionTable1 (col1 int, col2 varchar(max)) ON myRangePS1 (col1)

WITH ( DATA_COMPRESSION = ROW ON PARTITIONS (1), DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4)

);

GO

D. Setting the DATA_COMPRESSION option on a partitioned table

The following example uses the database that is used in example C. The example creates a table by using the syntax for noncontiguous partitions.

CREATE TABLE PartitionTable2 (col1 int, col2 varchar(max)) ON myRangePS1 (col1)

WITH (DATA_COMPRESSION = ROW ON PARTITIONS (1,3), DATA_COMPRESSION = NONE ON PARTITIONS (2,4)

);

GO

E. Modifying a table to change the compression

The following example changes the compression of the nonpartitioned table that is created in example A.

ALTER TABLE T1 REBUILD WITH (DATA_COMPRESSION = PAGE);

GO

F. Modifying the compression of one partition in a partitioned table

The following example changes the compression of the partitioned table that is created in example C. The REBUILD PARTITION = 1 syntax causes only partition number 1 to be rebuilt.

ALTER TABLE PartitionTable1 REBUILD PARTITION = 1 WITH (DATA_COMPRESSION =  NONE) ;

GO

The same operation that uses the following alternate syntax causes all partitions in the table to be rebuilt.

ALTER TABLE PartitionTable1 REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

GO

G. Modifying the compression of a several partitions in a partitioned table

The REBUILD PARTITION = ... syntax can rebuild only one partition. To rebuild more than one partition, you must execute multiple statements, or execute the following example to rebuild all partitions, using the current compression settings for unspecified partitions.

ALTER TABLE PartitionTable1 REBUILD PARTITION = ALL

WITH(DATA_COMPRESSION = PAGE ON PARTITIONS(1), DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)

) ;

GO

H. Modifying the compression on an index

The following example uses the table that is created in example A and creates an index on the column C2.

CREATE NONCLUSTERED INDEX IX_INDEX_1  ON T1 (C2) WITH ( DATA_COMPRESSION = ROW ) ;

GO

Execute the following code to change the index to page compression:

ALTER INDEX IX_INDEX_1 ON T1 REBUILD WITH ( DATA_COMPRESSION = PAGE ) ;

GO

I. Modifying the compression of a single partition in a partitioned index

The following example creates an index on a partitioned table that uses row compression on all partitions of the index.

CREATE CLUSTERED INDEX IX_PartTab2Col1 ON PartitionTable1 (Col1) WITH ( DATA_COMPRESSION = ROW ) ;

GO

To create the index so that is uses different compression settings for different partitions, use the ON PARTITIONS syntax. The following example creates an index on a partitioned table that uses row compression on partition 1 of the index and page compression on partitions 2 through 4 of the index.

CREATE CLUSTERED INDEX IX_PartTab2Col1 ON PartitionTable1 (Col1)

WITH (DATA_COMPRESSION = ROW ON PARTITIONS(1), DATA_COMPRESSION = PAGE ON PARTITIONS (2 TO 4 ) ) ;

GO

The following example changes the compression of the partitioned index.

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1 REBUILD PARTITION = ALL

WITH ( DATA_COMPRESSION = PAGE ON PARTITIONS(1) ) ;

GO

J. Modifying the compression of a several partitions in a partitioned index

The REBUILD PARTITION = ... syntax can rebuild only one partition. To rebuild more than one partition, you must execute multiple statements, or execute the following example to rebuild all partitions, using the current compression settings for unspecified partitions.

ALTER INDEX IX_PartTab2Col1 ON PartitionTable1 REBUILD PARTITION = ALL

WITH(DATA_COMPRESSION = PAGE ON PARTITIONS(1), DATA_COMPRESSION = ROW ON PARTITIONS(2 TO 4)

) ;

GO

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/24867586/viewspace-710841/，如需轉載，請註明出處，否則將追究法律責任。