Improving Availability and enhancing Programmability - Microsoft SQL Server 2008 R2 Administration - page 236

F The following points are considered for PAGE and ROW compression:

If vardecimal storage is used, then row-size-check is performed; when the
object is initially in compression and always checked as each row is inserted
or modified
The compression type can be set to ROW , PAGE, or NONE when a list of
partitions are specified. By default, a new table or index is created and the
data compression is always set to NONE unless otherwise specified
Non-clustered indexes do not inherit the compression property of the table
When a clustered index is created on a heap, it will inherit the compression
state of the heap unless the compression state is specified
When new pages are allocated in a heap, the DML operations will not
use PAGE compression until the heap is rebuilt (create and drop a
clustered index)
The disk space requirements must be calculated for row or page
compression, such as when creating or rebuilding an index

How to do it...

In this recipe, we will look at how to implement compressed storage features for tables
and indexes using simple TSQL statements such as CREATE TABLE and ALTER TABLE. The
procedure is as follows:

1. Create a new table and enable the row compression. To do so, we need to designate

the DATA_COMPRESSION table option using the value of either ROW or PAGE and
disable the compression by using NONE.

2. Open a new query window on SSMS and execute the following TSQL:

CREATE TABLE dbo.ArchiveSalesData
(SalesID INT not NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED,
ProductID int NOT NULL,
ProductName Char(60),
SalesQty int NOT NULL,
SaleDescription char(3000) NULL)

3. Let us input our data into 100,000 rows in the newly created object dbo.

ArchiveSalesData table. You can use the following TSQL to do so:
INSERT dbo.ArchiveSalesData (ProductID,ProductName,Sale
sQty) VALUES (CAST(RAND()*5 as INT), REPLICATE('Product
X',2),(CAST(RAND()*10 as INT)))
GO 100000

4. The INSERT script mentioned earlier will execute 100,000 times that will populate
100,000 rows into the dbo.ArchiveSalesData table.