Friday, November 16, 2012

Why Clustered Index Must Be Unique?

It is so that nonclustered index entries can point to exactly one specific row.

Some people may argue they can create clustered index upon non-unique column and without specifying unique keyword. That's true. In that case, SQL Server adds hidden unique identifier column to the rows when necessary.

Since clustered index keys are included in every nonclustered index, so that clustered index keys are the most duplicate and redundant data in a table. The choose of clustered index keys should be narrow so that it won't take too much storage.

Clustered index keys should also static so that it won't cause data relocation and page split, as well as updating nonclustered indexes.

Unique, narrow and static are also characters when a PK is defined.

No comments: