Clustered Index

What it does basically create a copy of table and sort it according to the specified column and delete the original table. So it help in searching and sorting the data against that column.
A table can have only one clustered index. The leaf level of a clustered index is the actual data and the data is resorted in case of clustered index.

If there is a primary key in table without specification of non clustered index and no clustered index exist on table than a clustered index automatically created on table.If no clustered index defined a table is called heap.


On which column we have to use Clustered Index? When we have to use clustered index?
1) The column which is used in search too frequently specifically in range search like date.
2) No need to create on Primary key it is automatically created for Primary Key if there is no other index created on any other column.You can override the default behavior of primary key by creating index on any other column, if you think it will improve performance.
3) Never create a cluster index on column which frequently updated since in case of clustered index it have to update the row in index table.
4) Create clustered index on a column which is used frequently for sorting the table.
5) Clustered index can be created on multiple column[Composite].
5) It is a good practice to create clustered index on each table.
6) Create a clustered index on a column which has sorted data somewhat.
7) Create a clustered index on a column which has minimal duplicate values.
6) Create a clustered index On a foreign key column.
7) Avoid creating a clustered index based on incrementing(Identity) column. When inserting and updating many rows at once it can create problem. As we know SQL Server automatically create a clustered index on primary key so explicitly specify non clustered key word to indicate that a primary key will use non clustered index.

Clustered index affect the table following way :

1) Reorder the data each time when you fired a query(Update,Delete)
2) Reorder the table according to clustered column.
3) Insert will be slow since the row will be inserted into right place.
4) If there are too many indexs(non clustered) than it affect(slow) the performance.
5) Every time the clustered index key is updated, SQL Server must maintain not just the clustered index but also the non-clustered indexes since non-clustered indexes contain a pointer to the clustered index instead of row pointer. This is yet another reason why you shouldn't create the clustered index on multiple columns.
6) Create a clustered index for each table, a table without clustered index is heap and data will not be sorted in any order and all row will be added at the end of table that can create the problem so better is that create a clustered index for each table if there is no index(by default) means no primary key.
7) If you are creating a clustered index on composite column suppose Name and Age. Than order of the column is very important here only queries that have Name(left most)in where clause will use the index.

Restrictions:
1) When creating an index using create statement you need to specify clustered by default it creates non clustered index.
2) Only owner of the table can create index.
3) Create Clustered index first than non clustered. Since if you will create clustered index after non clustered index it will update the non clustered index to replace row identifier with clustered index value.