Log Shipping – NORECOVERY vs. STANDBY mode

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 : http://technet.microsoft.com/en-us/library/dd578580(SQL.100).aspx