a. What is a clustered index?
b. How many clustered indexes can
be there for one table?
c. Can you tell some example on
clustered index that relates to real life experience?
d. When you declare primary key
constraint, then which index is created on that column?
- Even when the data is inserted in random order, it will be arranged as per the clustered index created on particular column. For example clustered index is created on empid of employee table , then even though the order of records(empid) inserted is say 1,4,5,3,2, But when the data will fetched from table through select query , you will see the order of records as 1,2,3,4,5. Data is automatically arranged on sequential ascending order after each insert.
- It’s similar to telephone directory in which data is arranged by the last name.
- Clustered index that includes more than one column are called Composite Clustered Index.
- Only one clustered index can be created on a table, before creating another, one has to drop the existing clustered index.
- Clustered index can be created on maximum 16 columns.
- Clustered index will always cover a query , since it contains all of the data in the leaf node itself, or should say table itself
for more questions and answers on indexes