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
- 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).
- 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.
Top comments (0)