You are a database developer on an instance of SQL Server 2008. You created the TrxHistory table using the following statement:
CREATE TABLE Inventory.TrxHistory (
ID int PRIMARY KEY,
TrxID int,
TrxDesc varchar(50),
TrxAmt money,
TrxDate datetime,
UpdID int);
The table has become extremely large and you want to minimize the storage it requires.
You want to determine the storage savings if you implemented page-level compression for the table.
Which statement should you use?
A. EXEC sp_estimate_data_compression_savings ‘Inventory’, ‘TrxHistory’, NULL, NULL, ‘PAGE’;
B. EXEC sp_estimate_data_compression_savings ‘Inventory.TrxHistory’, ‘PAGE’;
C. EXEC sp_estimate_data_compression_savings ‘Inventory’, ‘TrxHistory’;
D. EXEC sp_spaceused N’Inventory.TrxHistory’;
Correct Answer: A
Explanation/Reference:
You can use the sp_estimate_data_compression_savings system stored procedure to estimate the space that would be saved by implementing row or page compression for a table. You can also use the procedure to estimate the result of disabling compression or implementing compression for an index or a single partition of a partitioned table. The procedure accepts the following input parameters:
@schema_name: Indicates the schema that contains the table. This parameter defaults to the current user’s schema if a schema is not specified.
@object_name: Indicates the table name.
@index_id: Indicates the ID number of an index.
@partition_number: Indicates the partition number.
@data_compression: Indicates the type of compression. Valid values are NONE, ROW, and PAGE.
The procedure returns a column indicating the current size, size_with_current_compression_setting, and a column indicating the estimated size if the specified type of compression were implemented, size_with_requested_compression_setting. In this scenario, you would be able to determine the estimated savings if you implemented page-level compression for the TrxHistory table. You should note that when evaluating whether to implement compression, you should also consider how the data is accessed. Compression generates overhead, especially for tables that are frequently accessed. Therefore, both the storage savings and the performance impact should be considered.
You should not use either of the following statements:
EXEC sp_estimate_data_compression_savings ‘Inventory.TrxHistory’, ‘PAGE’;
EXEC sp_estimate_data_compression_savings ‘Inventory’, ‘TrxHistory’;
Both statements use invalid syntax, and would generate the following error:
Msg 201, Level 16, State 4, Procedure sp_estimate_data_compression_savings, Line 0Procedure or function
‘sp_estimate_data_compression_savings’ expects parameter ‘@index_id’, which was not supplied.
You should not use the following statement:
EXEC sp_spaceused N’Inventory.TrxHistory’;
The sp_spaceused system stored procedure returns current space usage information, not estimated storage savings by implementing compression.