There is no need for a clustered index to be unique !
However, if it isn't unique, sqlserver will "uniquify" it for you by adding a 4-byte part to it.
The clustered index actually contains the data pages at its leaf level.
All non-clustered indexes refer to the row using the uniquified clustered index key ! (so the wider your clix columns, the wider your ncix refs)
If there is no clustering index, these indexes refer to the row using the RID.Books online has great info on index structures and usage. !!
You need to keep in mind an index is unique over the combination of its columns, in the order the columns are specified !
If you need every column to be unique, you'll need to declare a unique index on each of the columns (or a unique constraint at column level)
- create a clustered index !
- keep your clix key columns small
- keep your clix unique if possible (avoind the uniquification by the engine)
- making your clix keys growing sequential may avoid run time page splits
- create a clustered index that serves you well ( range scans ). If you cannot find one to serve you well, just pick the one that goes for the previous guidelines.
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere
- How to post Performance Problems
- How to post data/code to get the best help
- How to prevent a sore throat after hours of presenting ppt ?press F1 for solution, press shift+F1 for urgent solution
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me
but most of the time this is me