SQL Server: Page Level Compression

Page compression is implemented internally by SQL Server to minimize the storage required for duplicate data. SQL Server uses a column-prefix or page-dictionary compression technique to eliminate redundant data within each page. In this scenario, because DBA/Developer has a large amount of duplicate data, DBA/Developer should use page compression. When a DBA/Developer implements page compression, SQL Server also implements row compression. Row compression saves storage space for each row by internally compressing each fixed-length numeric, date/time, or character data type. When DBA/Developer implements row compression for a table, columns in the table only use the actual space required, rather than using the number of bytes required for each data type. DBA/Developer can implement table compression when the table is created by specifying the DATA_COMPRESSION option in the CREATE TABLE statement. Valid options for the DATA_COMPRESSION option are as follows:

NONE: Implements no compression for the table.
ROW: Implements only row compression for the table.
PAGE: Implements row and page compression for the table.

In this scenario, DBA/Developer could create the SalaryAudit table with page-level compression using the following statement:

CREATE TABLE Salary Audit
(ID int PRIMARY KEY, EmpID int, LastName varchar(35),
FirstName varchar(25), DeptName varchar(30),OldAmt money, NewAmt money, UpdDate datetime,UpdID int)

WITH (DATA_COMPRESSION = PAGE);

DBA/Developer can also alter an existing table to use data compression. For example, DBA/Developer could use the following statement to modify a previously created table, Table1, to use row compression:

ALTER TABLE Table1
REBUILD WITH (DATA_COMPRESSION=ROW);

DBA/Developer should note that compression requires additional overhead, especially if the table is frequently accessed or modified, because the data must be compressed and uncompressed when it is accessed or modified. Therefore, DBA/Developer should closely evaluate not only the storage requirements but also how the table is used before implementing compression. In this scenario, the data is written only once to the table when a salary update occurs, and is rarely queried. Therefore, the additional overhead would likely have no adverse effects. DBA/Developer should not use FILESTREAM storage and implement NTFS compression. FILESTREAM storage is implemented to store large binary objects, such as image or video files, as files on the file system and be able to manage them using Transact-SQL. FILESTREAM data can also be accessed using Win32 APIs. FILESTREAM storage is tightly integrated with most database functionality, including backup and recovery. When a DBA/Developer takes a database backup, FILESTREAM storage is also backed up unless DBA/Developer override this functionality by performing a partial backup. To create a table that can store FILESTREAM data, DBA/Developer creates a table that contains a column of the varbinary(max) data type and include the FILESTREAM attribute. DBA/Developer cannot implement column-level compression for the table because column-level compression is not a compression strategy supported by SQL Server. SQL Server can implement compression at the row or page levels only. DBA/Developer should not set the PERSISTED property for the EmpID, LastName, FirstName, and UpdID columns. The PERSISTED property is only applicable to computed columns, which are not used in this scenario. Computed columns are virtual columns that by default are not physically stored in the table. Each computed column uses the AS keyword, followed by an expression that evaluates to a value. The expression can contain constants, functions, operators, and references to other columns within the table. The value of the computed column is calculated each time a query references it executes. Computed columns that specify the PERSISTED property are physically stored in the table and recalculated each time a column value referenced in the calculation expression is changed.