Clustered index Criteria

Scenarios to have Clustered indexes:

1) If the query has a WHERE clause with operators like BETWEEN, >, >=, <, and <=.
2) If the query is returns large result sets.
3) If the query uses JOIN clauses.
4) The query accesses values in a column sequentially. For example, a query on EmployeeIDs between 10000 and 900000
5) If the query uses an ORDER BY clause or a GROUP BY clause. The index might remove the need to sort the data because the data is already ordered.

Criteria to create Clustered index:

1) Contains unique values or, failing that, contain mostly distinct values.
2) Are defined as IDENTITY. Each value in the column is known to be unique.
3) Are often used to sort data returned by a query. Having data in a clustered index that is already sorted in a desired order can save the cost of sorts during the query.

Criteria to not create Clustered index:

1) There is a wide key for each data row.
2) Values in the key columns undergo frequent changes. Because data in a clustered index is sorted on key column values, a change in those values means that data rows have to be moved into their correctly sorted positions after any changes.

Clustered index Vs Non-Clustered index

Primarily in SQL server there are two types of indexes, clustered index and Non-Clustered index. Let me give you very basic difference between these types of indexes. These two indexes are different in terms how the data is stored. In Clustered index the leaf-nodes contains the data where as in Non-clustered index leaf-nodes contains the bookmarks to the actual data. In other words data is sorted and stored based on the Clustered Index. So that’s the reason you can have only one Clustered index per table.