What is the difference between a clustered and non-clustered index, and when would you use each?

In SQL, indexes are used to improve query performance by providing a faster way to retrieve data from tables. There are two main types of indexes: clustered and non-clustered.

Clustered indexes determine the physical order of the data in the table. When a clustered index is created on a table, the data in the table is physically reordered based on the indexed column. Only one clustered index can be created on a table, as the data can only be physically sorted in one way.

Here is an example of creating a clustered index:

CREATE CLUSTERED INDEX idx_employee_id ON employees (employee_id);

In this example, a clustered index called idx_employee_id is created on the employee_id column of the employees table. The data in the table is physically ordered based on the employee_id column, which can improve query performance when selecting data based on the employee_id column.

Non-clustered indexes, on the other hand, do not determine the physical order of the data in the table. Instead, they create a separate index structure that maps the indexed columns to the corresponding table data. Non-clustered indexes can be created on multiple columns in a table, and multiple non-clustered indexes can be created on a single table.

Here is an example of creating a non-clustered index:

CREATE NONCLUSTERED INDEX idx_last_name ON employees (last_name);

In this example, a non-clustered index called idx_last_name is created on the last_name column of the employees table. The index structure maps the last_name column values to the corresponding table data, allowing for faster retrieval of data based on the last_name column.

When to use each type of index depends on the specific requirements of the database and the queries that will be executed on the data. Clustered indexes are typically used on columns that are frequently searched or sorted, as they can improve performance by physically reordering the data. Non-clustered indexes are typically used on columns that are frequently searched but not sorted, as they create a separate index structure that can be searched more quickly than the table data itself.

In summary, clustered indexes physically reorder the data in the table and are best used for frequently searched or sorted columns. Non-clustered indexes create a separate index structure and are best used for frequently searched but not sorted columns.