SQL Server: Database Table Indices

Partitioned tables and indices can use different types of compression for individual partitions. DBA/Developer 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 DBA/Developer 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. DBA/Developer 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. DBA/Developer 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. DBA/Developer should not implement a partitioned view. Partitioned views are used when DBA/Developer 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 the UNION ALL operator. Although in some situations a partitioned view is helpful, using one would not be applicable in this scenario. DBA/Developer should not create a filtered index for each partition. A filtered index is a non clustered 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.