Difference Between Clustered and Non-clustered index
only one clustered index per table where as you can have more than one non clustered index per table
cluster index faster than non-cluster index because the clustered index has to refer back to the table, if the selected column is not present in the index
cluster index determines the storage order of row in a table hence does not require additional disk space but a non-clustered index is stored separately from the table,additional storage is required
A clustered index physically sort all rows while Non clustered Index doesn’t
Clustered Index is automatically created on the primary key column
2 comments
Well, I understand how you think, but you’re a bit wrong. A table have different physical forms: Heap, Clustered Index, Clustered Columnstore Index, In-Memory OLTP Table and so on.
So, the reason why you only can have one clustered index is due to the fact it is the table itself. When you build or rebuild the clustered index the rows are sorted. But it only guarantees that the rows are grouped in a data page, and if a data page is full and you add a row that should be in that data page, the page is split up in two pages. This is a page split.
Non clustered indexes do only consist of index key columns, pointers into clustered indexes or the specific row in a specific page, and if you so want included columns. They are redundant to the table, but points to where the row is stored.
As an example, if your clustered index has a broken data page you actually loose data, if your non clustered index has a broken data page, this is index leaf page, you just rebuild the non clustered index.
Sorting/Ordering of rows are only done when you query the data and add ORDER BY, or if you have join operations that need the data to be ordered, or if you group rows when doing aggregations… There are some more scenarios when data is ordered. But remember that when you have indexes you might experience that data is ordered, merely because the data is unchanged since index rebuild and all extents are in order, and all extents are uniform.
The clustered index inflicts in what page the row should be placed in, not in what order within the page.
Well, I understand how you think, but you’re a bit wrong. A table have different physical forms: Heap, Clustered Index, Clustered Columnstore Index, In-Memory OLTP Table and so on.
So, the reason why you only can have one clustered index is due to the fact it is the table itself. When you build or rebuild the clustered index the rows are sorted. But it only guarantees that the rows are grouped in a data page, and if a data page is full and you add a row that should be in that data page, the page is split up in two pages. This is a page split.
Non clustered indexes do only consist of index key columns, pointers into clustered indexes or the specific row in a specific page, and if you so want included columns. They are redundant to the table, but points to where the row is stored.
As an example, if your clustered index has a broken data page you actually loose data, if your non clustered index has a broken data page, this is index leaf page, you just rebuild the non clustered index.
Sorting/Ordering of rows are only done when you query the data and add ORDER BY, or if you have join operations that need the data to be ordered, or if you group rows when doing aggregations… There are some more scenarios when data is ordered. But remember that when you have indexes you might experience that data is ordered, merely because the data is unchanged since index rebuild and all extents are in order, and all extents are uniform.
The clustered index inflicts in what page the row should be placed in, not in what order within the page.
Thanks for your suggestion.