Let's walk through an example with Employee ID as the Clustered Index and Employee Name as the Non-Clustered Index to illustrate the concepts more clearly.

Sample Table: Employees

Step 1: Clustered Index on emp_id

When we create a Clustered Index on emp_id, the rows of the Employees table will be physically stored on disk in ascending order of emp_id. The Clustered Index determines the physical order of the data in the table.

So, after creating a Clustered Index on emp_id, the data rows in the table will be stored in the following order:

Key Concept: Since emp_id is the Clustered Index, the table rows are physically sorted by emp_id.

Step 2: Non-Clustered Index on emp_name

Now, let's create a Non-Clustered Index on emp_name. This Non-Clustered Index will not change the physical storage of the table rows but will create a separate index structure that stores the emp_name values along with pointers to the corresponding rows in the table.

The Non-Clustered Index on emp_name would look something like this:

Key Concept: The Non-Clustered Index is a separate structure that stores the emp_name values in alphabetical order along with a pointer to the row that contains the actual data (in this case, the emp_id and other columns).

Note: The rows in the table are still physically sorted by emp_id (due to the Clustered Index), but the Non-Clustered Index on emp_name is stored separately and allows for quick lookups based on employee names.

Usage

If you query using emp_id (e.g., SELECT * FROM Employees WHERE emp_id = 102), the database can quickly find the row using the Clustered Index on emp_id because the data is physically sorted by emp_id.

If you query using emp_name (e.g., SELECT * FROM Employees WHERE emp_name = 'Alice Smith'), the database will use the Non-Clustered Index on emp_name to quickly find the row. The index will point to emp_id = 103, and then the database will retrieve the corresponding data row.

Summary of the Example

1. Clustered Index on emp_id:

The table rows are physically sorted by emp_id.

There can only be one clustered index on the table (since the physical order can only be sorted one way).

1. Non-Clustered Index on emp_name:

The index is separate from the table and stores emp_name values in sorted order, along with pointers to the actual rows.

The table rows themselves are not sorted by emp_name; instead, the index allows for quick lookups based on emp_name.

Key Difference Illustrated

Clustered Index: Determines how the data is physically stored on disk.

Non-Clustered Index: Provides a separate index structure to improve query performance, without changing the physical order of the data in the table.