What is the difference between a clustered and non-clustered index?

The main difference between a clustered index and a non-clustered index is how they are physically stored in the database.

A clustered index is a special type of index that also stores the physical data of the table in the same order as the index. This means that the data in the table is physically sorted in the same order as the index. Clustered indexes can improve performance for queries that retrieve a large number of rows from the table, because the database can use the index to avoid having to scan the entire table.

A non-clustered index does not store the physical data of the table. Instead, it stores a pointer to the actual data. Non-clustered indexes can improve performance for queries that retrieve a small number of rows from the table, because the database can use the index to quickly find the data that you need.

Here is a table that summarizes the key differences between clustered and non-clustered indexes:

Feature Clustered index Non-clustered index
Physical storage Stores the physical data of the table in the same order as the index. Does not store the physical data of the table. Instead, it stores a pointer to the actual data.
Performance impact Can improve performance for queries that retrieve a large number of rows from the table. Can improve performance for queries that retrieve a small number of rows from the table.
Number of indexes allowed Only one clustered index is allowed per table. Multiple non-clustered indexes can be created per table.
When choosing which type of index to create, you need to consider the following factors:

Which columns should be indexed? You should index the columns that are most frequently used in your queries.
Should the index be clustered or non-clustered? If you need to retrieve a large number of rows from the table, then you should consider creating a clustered index. If you need to retrieve a small number of rows from the table, then you should consider creating a non-clustered index.