Thursday, January 19, 2012

Indexes and Constraints on a Partitioned Table

In a partitioned table, the partition column must be a part of:

  •     The clustered index key.
  •     The primary key.
  •     Unique index and uniqueness constraint keys.

The partition column must be part of the clustered index key. This makes sense because one of the purposes of a clustered index is to physically organize the pages of a table, and partitioning affects the physical structure of a table as well. SQL Server will internally enforce that the partition column is part of the clustered key, when the table is placed on the partition scheme and the clustered index is created.

The partition column must also be part of the primary key of the partitioned table, if one is declared, whether the primary key is clustered or no clustered. A primary key has an underlying index to enforce uniqueness. You can place the partitioned column after the original primary key columns.

Any unique index must also have the partition column as part of its key, so that SQL Server can enforce uniqueness across the entire set of partitions. Therefore any uniqueness constraint must also have the partition column as part of its key. If your unique index or constraint cannot contain the partitioned column, you can enforce the uniqueness using a DML trigger.

For secondary indexes that are not unique or clustered, the requirements are relaxed somewhat. Still, the benefits of including the partition column in a secondary index can be significant. When secondary indexes have the partition column as a part of their key, and use the same or equivalent partition scheme, the indexes are partitioned and are said to be aligned with the underlying object (heap or clustered index). SQL Server automatically adds the partition column to a secondary nonunique index as an included column if the CREATE INDEX statement does not already contain the partition column.

A secondary index does not have to use the same partition function as the underlying partitioned table to achieve index alignment, as long as the partition function used by each has the same characteristics (equivalent data types of the partition column, number and values of the boundary values, and range direction.) However, it is much more convenient to use the same partition function and partition scheme for the indexes and the underlying partitioned table.

Index alignment helps in achieving partition elimination, where the query processor can eliminate inapplicable partitions from a query plan to access just the partitions required by the query. Index alignment is also required for using the SWITCH statement, which we’ll cover in the next section. If you have a nonaligned secondary index on a table and need to use the SWITCH option, you can always disable the index during the switch process and re-enable it when done.

More details :

What’s new on SQL SERVER 2008 SP3

Microsoft SQL Server Sustained Engineering group is pleased to announce the release of SQL Server 2008 Service Pack 3. Both the Service Pack and Feature Pack updates are available for download on the Microsoft Download Center. As part of our continued commitment to software excellence for our customers, this upgrade is free and doesn’t require an additional service contract.

SQL Server 2008 SP3 contains fixes to issues that have been reported through our customer feedback platforms and Hotfix solution provided in SQL Server 2008 SP2 Cumulative Update 1 thru to Cumulative Update 4.  Service Pack 3 also includes supportability enhancements and issues that have been reported through Windows Error Reporting system.

A few customers requested updates in Microsoft SQL Server 2008 SP3 are:

  • Enhanced upgrade experience from previous versions of SQL Server to SQL Server 2008 SP3. In addition, we have increased the performance & reliability of the setup experience.
  • In SQL Server Integration Services logs will now show the total number of rows sent in Data Flows.
  • Enhanced warning messages when creating the maintenance plan if the Shrink Database option is enabled.
  • Resolving database issue with transparent data encryption enabled and making it available even if certificate is dropped.
  • Optimized query outcomes when indexed Spatial Data Type column is referenced by DTA (Database Tuning Advisor).
  • Superior user experience with Sequence Functions (e.g Row_Numbers()) in a Parallel execution plan.

To obtain SQL Server 2008 SP3 with its improved security and supportability please visit the links below: