Explain about Clustered and non clustered index? How to choose between a Clustered Index and a Non-Clustered Index?

There are clustered and nonclustered indexes. A clustered index is a special type of index that reorders the way records in the table are physically stored. Therefore table can have only one clustered index. The leaf nodes of a clustered index contain the data pages.

A nonclustered index is a special type of index in which the logical order of the index does not match the physical stored order of the rows on disk. The leaf nodes of a nonclustered index does not consist of the data pages. Instead, the leaf nodes contain index rows.

Consider using a clustered index for:

• Columns that contain a large number of distinct values.
• Queries that return a range of values using operators such as BETWEEN, >, >=, <, and <=.
• Columns that are accessed sequentially.
• Queries that return large result sets.
  Non-clustered indexes have the same B-tree structure as clustered indexes, with two significant differences:
• The data rows are not sorted and stored in order based on their non-clustered keys.
• The leaf layer of a non-clustered index does not consist of the data pages. Instead, the leaf nodes contain index rows. Each index row contains the non-clustered key value and one or more row locators that point to the data row (or rows if the index is not unique) having the key value.

Per table only 249 non clustered indexes.