Sql Server: index part 2

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?

Clustered Index

Clustered index is created on Primary key automatically, if there is no clustered index already exists on table. This index determines physical order of data in a table. As there can be only one way, in which data can be arranged on disk or memory, therefore there can be only one clustered index on each table.

One can create clustered index explicitly as shown below

create clustered index indexname on [TableName](ColumnName)

 Some points on clustered indexes

•  Even when the data is inserted in random order, it will be arranged as per the clustered index created on a particular column. For example, a clustered index is created on empid of employee table, then even though the order of records(empid) inserted is for e.g. 1,4,5,3,2, But when the data will be fetched from the table through a 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 a telephone directory in which data is arranged by the last name.

• Clustered an 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. 

• A clustered index can be created on a maximum of 16 columns.

• Clustered the index will always cover a query, since it contains all of the data in the leaf the node itself, or should say table itself

for more questions and answers on indexes
Previous      Next

Understanding on Indexes