You are a database developer on an instance of SQL Server 2008. You have a partitioned table named TransAudit that contains historical audit records. Older audit records are rarely accessed. The TransAudit table is partitioned as follows:
You want to minimize storage required for the TransAudit table. Older audit records that reside in partition #1 were originally imported into the table from a legacy system using an SSIS package.
You analyze the underlying data and decide to implement row-level compression for partition #1. You also want to implement row-level and page-level compression on partition #2, but no compression on partition #3 because it is more frequently accessed.
You want to perform this task with minimal effort and resource usage.
Which Transact-SQL should you use?
A. ALTER TABLE TransAudit
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION=ROW);
ALTER TABLE TransAudit
REBUILD PARTITION = 2 WITH (DATA_COMPRESSION=BOTH);
B. ALTER TABLE TransAudit
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION=BOTH);
ALTER TABLE TransAudit
REBUILD PARTITION = 2 WITH (DATA_COMPRESSION=PAGE);
C. ALTER TABLE TransAudit REBUILD PARTITION ALL
WITH (DATA_COMPRESSION=ROW ON PARTITIONS(1),
DATA_COMPRESSION=PAGE ON PARTITIONS(2));
D. ALTER TABLE TransAudit
REBUILD PARTITION = 1 WITH (DATA_COMPRESSION=ROW);
ALTER TABLE TransAudit
REBUILD PARTITION = 2 WITH (DATA_COMPRESSION=PAGE);
