DDL : Clustered vs non-clustered indexes

Typically, a clustered index will be created on the primary key of a table, and non-clustered indexes are used where needed.

Non-clustered indexes
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'
Clustered indexes
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.