SQL Server 2008 requires unique non-clustered indexes created on a partition to include the partition field in the index definition.
I'm wondering why.
If the clustering key would not be part of the key set then the index uniqueness across all partitions could not be guaranteed.
The long story:
The only way to guarantee uniqueness is by enforcing uniqueness via a B-Tree structure. With a B-Tree is really easy to enforce uniqueness, since any key has a deterministic logical position in the B-Tree (between its left neighbor, that is smaller key, and the right neighbor, which is a larger key). Since each key value can be inserted in a single position in the tree then uniqueness is easy to guarantee: simply check during insert if the position is already occupied by an existing row.
With partitioning uniqueness across all partitions is trickier, since there are many B-Tree structures involved (one for each partition). Each can guarantee uniqueness, within a partition, but together they cannot guarantee global uniqueness across all partitions: a key in partition X may also appear in partition Y. Therefore, in order to preserve the semantics of 'unique index', the clustering column must be part of the key. This way each key value deterministically bound to a specific partition: a key cannot appear in partition X and Y because the partitioning column is part of the key, therefore the key can belong to exactly partition X or Y.
@MarkStoreySmith asked: So why doesn't SQL Server check for uniqueness by navigating each of the B-tree structures, acquiring range locks where the value being tested is not present, and holding until each B-tree has been traversed? Seeking each B-Tree would be a performance hog (think 15,000 partitions to be checked at each insert). And also the locking complications (hopping from one B-Tree to the next) are significant, with huge potential for deadlocks.
you can create the non-clustered index to align the partition or not.
if you create the align partition index, in SQL Server 2005, the partition filed is actual the last filed in the index leaf page, although not in the create statements. I verified this by running 'dbcc page'.
That could be simply because the index needs to be aligned with the partitions, as the index itself needs to be partitioned (i.e stored in separate locations on the drive). If the index is not aligned there will be cross partition pointers.