Clustered Index: index entries are actually data records and the database table is physically sorted on the basis of that index. Therefore when the database searches for an entry, it will end up with the actual data a lot faster. There can be only one Clustered index for a table. So this is usually made on the primary key.
- Columns that contain a large number of distinct values
- Queries that return a range of values using operators such as BETWEEN, >, <, >=, <=
- Columns that are accessed sequentially
- Queries that return large result sets
Non- Clustered Index: Index entries are not actually data records and index is UNSORTED. In here logical order of the index does not match with the physical stored order of the rows on the disk. That means the index data is stored outside the table and contains a sorted reference to the table. There can be many Clustered indexes for a table.
Ex: Clustered index is like a phone directory, where actual records are sorted by name. Non- clustered index is like an 'Index' in the end of a book, where actual records are not sorted. But you have sorted references to the book content.
Note: Clustered Index is an SQL Server term and is not like an Oracle cluster, but more like an Oracle Index Organized Table (IOT).
Indexing the table makes the data retrieval faster but it has a disadvantage that the insert and update statements start to take longer as all the indexes are updated and this effect is different in the two approaches. Because in clustered index you'll have to split the pile in the middle and insert/update records. In non-clustered it is only updating sorted references.
