Typically, a clustered index will be created on the primary key
of a table, and non-clustered indexes are used where needed.
- Leaves are stored in b-tree
- Lower overhead on inserts, vs clustered
- Best for single key queries
- Last page of index can become a 'hot spot'
- Records in table are sorted physically by key values
- Only one clustered index per table
- Higher overhead on inserts, if re-org on table is required
- Best for queries requesting a range of records
- Index must exist on same segment as table
Note! With "lock datapages" or "lock datarows" ... clustered indexes
are sorted physically only upon creation. After that, the indexes
behave like non-clustered indexes.