--- title: "What is SQL Clustered and Non-Clustered key?" description: "What is SQL Clustered and Non-Clustered key?" author: "ICSM Computer" published: 2026-03-22 updated: 2026-03-22 canonical: https://www.mindstick.com/interview/34479/what-is-sql-clustered-and-non-clustered-key category: "database" tags: ["database", "sql server"] reading_time: 6 minutes --- # What is SQL Clustered and Non-Clustered key? In **SQL Server**, Clustered and Non-Clustered keys are related to **indexes**. Indexes are used to **improve query performance** and **speed up data searching** in tables. These are used in **Microsoft SQL Server** and managed using **SQL Server Management Studio**. > Note: Correct technical term is **Clustered Index** and **Non-Clustered Index**, but many people call them **Clustered key / Non-clustered key**. ## 1. What is Clustered Key (Clustered Index)? A **Clustered Index** defines the **physical order of data in the table**. This means: - Data in table is stored in sorted order - Only ONE clustered index allowed per table ### Example ```plaintext CREATE TABLE Students ( Id INT PRIMARY KEY, Name VARCHAR(50), Age INT ) ``` Here, Primary Key creates **Clustered Index by default**. Data will be stored like: ```plaintext 1 Rahul 2 Amit 3 Neha 4 Riya ``` Sorted by Id. ### Key Points - Only one per table - Data stored in sorted order - Faster for range queries - Primary Key → Clustered by default ## 2. What is Non-Clustered Key (Non-Clustered Index)? - A **Non-Clustered Index** does NOT change physical order of table. - It creates a **separate structure** that points to actual data. Think like: > Book index page → points to page number Table data stays same, index stores pointers. ### Example ```plaintext CREATE NONCLUSTERED INDEX IX_Name ON Students(Name) ``` - Now searching by Name will be faster. - Data order in table does not change. ### Key Points - Multiple allowed - Data not physically sorted - Stored separately - Faster search on specific column ## 3. Difference Between Clustered and Non-Clustered | Feature | Clustered | Non-Clustered | | --- | --- | --- | | Physical order | Yes | No | | Number per table | 1 | Many | | Default on PK | Yes | No | | Storage | With table | Separate | | Speed | Fast for range | Fast for search | | Pointer needed | No | Yes | ## 4. Real Example Table: Employees ```plaintext Id Name Salary 3 Amit 20000 1 Rahul 30000 2 Neha 25000 ``` ### Clustered on Id Stored as: ```plaintext 1 Rahul 2 Neha 3 Amit ``` ### Non-Clustered on Name Index: ```plaintext Amit → row 3 Neha → row 2 Rahul → row 1 ``` ## 5. When to Use Clustered Index Use when: - Primary key column - Frequently sorted column - Range queries used - Unique values Example: - Id - Date - OrderNumber ## 6. When to Use Non-Clustered Index Use when: - Search by Name - Search by Email - Search by Status - Filter queries Example: ```plaintext WHERE Name = 'Rahul' WHERE Email = 'test@gmail.com' WHERE Status = 1 ``` ## 7. Clustered vs Heap - Table without clustered index = Heap - Heap is slower for search. ## 8. Conclusion - Clustered Index → sorts actual data - Non-Clustered Index → creates pointer index Both are important for performance tuning in SQL. ## Answers ### Answer by ICSM Computer In **SQL Server**, Clustered and Non-Clustered keys are related to **indexes**. Indexes are used to **improve query performance** and **speed up data searching** in tables. These are used in **Microsoft SQL Server** and managed using **SQL Server Management Studio**. > Note: Correct technical term is **Clustered Index** and **Non-Clustered Index**, but many people call them **Clustered key / Non-clustered key**. ## 1. What is Clustered Key (Clustered Index)? A **Clustered Index** defines the **physical order of data in the table**. This means: - Data in table is stored in sorted order - Only ONE clustered index allowed per table ### Example ```plaintext CREATE TABLE Students ( Id INT PRIMARY KEY, Name VARCHAR(50), Age INT ) ``` Here, Primary Key creates **Clustered Index by default**. Data will be stored like: ```plaintext 1 Rahul 2 Amit 3 Neha 4 Riya ``` Sorted by Id. ### Key Points - Only one per table - Data stored in sorted order - Faster for range queries - Primary Key → Clustered by default ## 2. What is Non-Clustered Key (Non-Clustered Index)? - A **Non-Clustered Index** does NOT change physical order of table. - It creates a **separate structure** that points to actual data. Think like: > Book index page → points to page number Table data stays same, index stores pointers. ### Example ```plaintext CREATE NONCLUSTERED INDEX IX_Name ON Students(Name) ``` - Now searching by Name will be faster. - Data order in table does not change. ### Key Points - Multiple allowed - Data not physically sorted - Stored separately - Faster search on specific column ## 3. Difference Between Clustered and Non-Clustered | Feature | Clustered | Non-Clustered | | --- | --- | --- | | Physical order | Yes | No | | Number per table | 1 | Many | | Default on PK | Yes | No | | Storage | With table | Separate | | Speed | Fast for range | Fast for search | | Pointer needed | No | Yes | ## 4. Real Example Table: Employees ```plaintext Id Name Salary 3 Amit 20000 1 Rahul 30000 2 Neha 25000 ``` ### Clustered on Id Stored as: ```plaintext 1 Rahul 2 Neha 3 Amit ``` ### Non-Clustered on Name Index: ```plaintext Amit → row 3 Neha → row 2 Rahul → row 1 ``` ## 5. When to Use Clustered Index Use when: - Primary key column - Frequently sorted column - Range queries used - Unique values Example: - Id - Date - OrderNumber ## 6. When to Use Non-Clustered Index Use when: - Search by Name - Search by Email - Search by Status - Filter queries Example: ```plaintext WHERE Name = 'Rahul' WHERE Email = 'test@gmail.com' WHERE Status = 1 ``` ## 7. Clustered vs Heap - Table without clustered index = Heap - Heap is slower for search. ## 8. Conclusion - Clustered Index → sorts actual data - Non-Clustered Index → creates pointer index Both are important for performance tuning in SQL. --- Original Source: https://www.mindstick.com/interview/34479/what-is-sql-clustered-and-non-clustered-key Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.