You are a database developer on an instance of SQL Server 2008. You are creating a SalaryAudit table to record custom audit information about salary changes for employees.
You have two payroll clerks that are allowed to process salary updates for the twenty departments within your company. At regular intervals, the clerks process salary updates for a large number of employees.
The table will contain the following columns:
This audit information will be rarely used, but internal auditors will perform ad hoc queries on the table during company-wide audits that occur every three years.
After each audit is complete and the finalized audit reports have been filed, the SalaryAudit table will be backed up, and all rows for the audit period will be removed from the table.
You want to minimize storage requirements for the table as much as possible, but support the other requirements with minimal effort.
Which action should you take?
A. Use FILESTREAM storage and implement NTFS compression.
B. Implement column-level compression for the table.
C. Implement page-level compression for the table.
D. Set the PERSISTED property for the EmpID, LastName, FirstName, and UpdID columns.
Correct Answer: C
Explanation/Reference:
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 you have a large amount of duplicate data, you should use page compression. When you implement 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 you implement 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.
You 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, you could create the SalaryAudit table with page-level compression using the following statement:
CREATE TABLE SalaryAudit (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);
You can also alter an existing table to use data compression. For example, you could use the following statement to modify a previously created table, Table1, to use row compression:
ALTER TABLE Table1 REBUILD WITH (DATA_COMPRESSION=ROW);
You 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, you 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.
You 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 you take a database backup, FILESTREAM storage is also backed up unless you override this functionality by performing a partial backup. To create a table that can store FILESTREAM data, you create a table that contains a column of the varbinary(max) data type and include the FILESTREAM attribute.
You 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.
You 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.
