Basic difference between a Clustered Index and a Non-Clustered Index

In a database, an index is a data structure that allows for quick access to specific data within a table. Indexes can be categorized into two types: clustered and non-clustered indexes.

Clustered Index

A clustered index determines the physical order of data in a table. This means that the data in the table is physically organized based on the values in the index. In other words, a clustered index reorders the data in the table itself to match the order of the index. A table can have only one clustered index, and it's generally created on the primary key of the table.

Example

To create a clustered index in a database, you can use SQL commands. Here's an example of the SQL command to create a clustered index on a table named "example_table" with a primary key column named "id":

CREATE CLUSTERED INDEX idx_example_table_id ON example_table (id);

In this example, "idx_example_table_id" is the name of the clustered index that you are creating, and "id" is the name of the primary key column in the "example_table" table. You can replace "example_table" and "id" with the name of the table and primary key column you want to create the clustered index on.

Non-Clustered Index

A non-clustered index creates a separate structure that contains the index data, rather than physically ordering the table data. The index data structure contains a copy of the indexed column(s) and a reference to the corresponding table data. This allows for faster data retrieval based on the indexed column(s) as the database engine can use the index to locate the table data more quickly. A table can have multiple non-clustered indexes.

Example

To create a non-clustered index in a database, you can use SQL commands. Here's an example of the SQL command to create a non-clustered index on a table named "example_table" with a column named "name":

CREATE INDEX idx_example_table_name ON example_table (name);

In this example, "idx_example_table_name" is the name of the non-clustered index that you are creating, and "name" is the name of the column in the "example_table" table. You can replace "example_table" and "name" with the name of the table and column you want to create the non-clustered index on.

Also, keep in mind that creating too many indexes on a table can negatively impact the database's write performance as it can increase the time taken to insert and update data. Therefore, it's important to strike a balance between the number of indexes and their usefulness in improving query performance.