You are a database developer on an instance of SQL Server 2008. You have a SalesHistory table partitioned as follows:
You currently have an index on the SalesHistory table that implements row-level compression for partition #1 of the index. You want to optimize the index further by implementing page-level compression for the other partitions of the index. Which action should you take?
A. Rebuild the partition function and the SalesHistory table.
B. Alter the index to use different compression settings for individual partitions.
C. Implement a partitioned view.
D. Create a filtered index for each partition.
Correct Answer: B
Explanation/Reference:
In this scenario, you have a partitioned index on the SalesHistory table that implements row compression for partition #1 of the index. Partitioned tables and indexes can use different types of compression for individual partitions. You can modify these compression settings using the ALTER TABLE and ALTER INDEX statements. The ON PARTITIONS clause specifies the partition(s) to which settings apply. For example, in this scenario, suppose you used the following statement to create the existing partitioned index:
CREATE CLUSTERED INDEX IX_SalesHistoryOrderID ON SalesHistory (OrderID)WITH (DATA_COMPRESSION = ROW ON PARTITIONS (1),DATA_COMPRESSION = NONE ON PARTITIONS (2 TO 3));
The index would use row compression for partition #1 of the index, and no compression for the other partitions. You could use the following statement to modify the partitioned index to use page compression for partitions #2 and #3:
ALTER INDEX IX_SalesHistoryOrderID ON SalesHistory REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE ON PARTITIONS(2 TO 3));
Row compression is used to minimize storage at the row level by compressing fixed-length data types. Page compression is used to minimize storage for redundant data stored in pages.
You should not rebuild the partition function and the SalesHistory table. There is no need to rebuild the partition function. The partition function defines the boundary values for a partitioned table. In this scenario, the partition boundaries were not changed.
You should not implement a partitioned view. Partitioned views are used when you have similar data stored in multiple tables and want to create a view to allow access to all of the data as if it were stored in a single table. Partitioned views, especially distributed partitioned views that access data across multiple servers, can significantly improve scalability. Partitioned views are implemented by creating a view that queries several tables and combines the results using theUNION ALL operator. Although in some situations a partitioned view is helpful, using one would not be applicable in this scenario.
You should not create a filtered index for each partition. A filtered index is a nonclustered index that is defined including a specific WHERE clause to optimize the index for specific queries. The index uses the WHERE clause condition to index only specific rows in the table. Using a filtered index can improve performance in situations in which specific subsets of the data are frequently accessed, and can reduce the space required for the index. However, in this scenario, a filtered index would not be applicable.
