Monday, August 26, 2013

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 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
Previous      Next

No comments:

Post a Comment