The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P56

The Real MTCS SQL Server 2008 Exam 70/432 Prep Kit- P56: Congratulations on your journey to become certified in SQL Server 2008. This book will help prepare you for your exam and give you a practical view of working with SQL Server 2008. | Maintaining Your Database Chapter 7 257 As you have learned from earlier chapters tables can be stored as a heap where rows are stored in no particular order or as a clustered index where rows are stored in the order defined by the index. You can use compression on both types of table. Nonclustered indexes are stored separately from the table on which they are defined. Nonclustered indexes can also be created on views a situation referred to as indexed views . You can use compression on nonclustered indexes for tables and views. Finally data compression can be configured for individual partitions that tables and indexes are stored across. For example if a table is partitioned into current and historical partitions you can choose to enable data compression on the historical partition only. To enable compression on a table or index use the CREATE TABLE or CREATE INDEX statement with DATA_COMPRESSION ROW PAGE option. If you are enabling compression on an existing table or index use the DATA_COMPRESSION option with the ALTER TABLE or ALTER INDEX statement. Enabling compression will cause a rebuild of the object and is therefore a highly time and resource consuming operation. Enabling data compression on a table will have no effect on that table s non clustered indexes. Each non clustered index can be compressed separately. The syntax for enabling data compression is shown in Examples through . Example Enabling Data Compression on a New Table Syntax CREATE TABLE database_name . schema_name .table_name Column Definition List WITH DATA_COMPRESSION ROW PAGE Example Enabling Data Compression on an Existing Table Syntax ALTER TABLE database_name . schema_name .table_name REBUILD WITH DATA_COMPRESSION ROW PAGE NONE Example Enabling Data Compression on a New Nonclustered Index Syntax CREATE NONCLUSTERED INDEX index_name ON table_name Column List WITH DATA_COMPRESSION ROW PAGE Example Enabling Data Compression on an Existing Nonclustered Index Syntax .