Be responsible for your future
Enter the USA legally!
Visa, Green Card, Citizenship, Passport
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.
- 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.
Get the latest Rocket99 news and tech tips via