You are a database developer on an instance of SQL Server 2008. You have a database that contains a large table named InvTransaction.
The InvTransaction table contains historical data on inventory transactions that previously occurred. The table is queried occasionally and used to produce monthly reports, but is very rarely modified.
The InvTransaction table is defined as follows:
The InvTransaction table contains millions of rows, and has the following additional characteristics:
• A clustered index exists on the TranID column.
• The LocationID column for most transactions has a value of 1.
• The TranType column for most transactions has a value of ‘T1’.
• Each BinID column has a bin value between 1 and 100 because there are a maximum of 100 bins for each location.
• Most of the TranDetails column values have similar descriptions.
You want to minimize the amount of storage used by the table, but not directly modify any of the table’s columns.
Which action should you take?
A. Implement sparse columns for the LocationID, TranType, and BinID columns.
B. Set the text in row option to OFF for the InvTransaction table.
C. Issue the ALTER TABLE REBUILD statement with the DATA_COMPRESSION=PAGE option for the InvTransaction table.
D. Issue the ALTER TABLE REBUILD statement with the DATA_COMPRESSION=ROW option for the InvTransaction table.
Correct Answer: C
Explanation/Reference:
You should issue the ALTER TABLE REBUILD statement with the DATA_COMPRESSION=PAGE option for the InvTransaction table. In this scenario, you have rows with LocationID, TranType, and BinID values that are small. Therefore, you should implement row compression. Row compression saves storage space for each row by internally compressing each fixed-length numeric, date/time, or character data type. For example, if you implemented row compression for the InvTransaction table, the LocationID and BinID columns would use only the space required, rather than using the four bytes that an int data type requires. Because most rows have a LocationID value of 1 and a TranType value between 1 and 100, three bytes per column would be saved for most rows in the table.
In this scenario, you also have data that is frequently duplicated within the LocationID, TranType, BinID, and TranDetails columns. Therefore, you should also implement page compression. Page compression is implemented internally by SQL Server to minimize the storage required to store duplicated data. SQL Server uses a column-prefix or page-dictionary compression technique to eliminate redundant data within each page.
When you implement page compression, SQL Server also implements row compression. Therefore, in this scenario, rebuilding the table with page-level compression would perform both types of compression and minimize storage for the InvTransaction table. The syntax of rebuilding a table with compression is as follows:
ALTER TABLE table_name
REBUILD WITH (DATA_COMPRESSION = {NONE | ROW | PAGE});
You should not implement sparse columns for the LocationID, TranType, and BinID columns. When creating a table, sparse columns can be used to optimize the storage of NULL values and minimize storage requirements. This would not be applicable in this scenario because there were no columns mentioned that contained mostly NULL values. In addition, you did not want to modify any of the table’s columns directly.
You should not set the text in row option to OFF for the InvTransaction table. The text in row option is used to control whether text, ntext, or image columns are stored in the data row of the table. You could store data out-of-row to minimize storage used by the table. But in this scenario, setting this option would not be appropriate because you do not have any text, ntext, or image columns in the InvTransaction table.
You can also use the large value types out of row option to specify whether large value types, such as varchar(max), nvarchar(max), and varbinary(max), are stored in the data row.
You should not issue the ALTER TABLE REBUILD statement with the DATA_COMPRESSION=ROW option for the InvTransaction table. This would only implement row compression for the table. In this scenario, implementing page compression would further minimize storage requirements.
