Clustered vs Non-Clustered Index

 Indexes are essential for improving the performance of SQL queries. There are two main types of indexes in SQL Server: clustered and non-clustered indexes. Understanding the differences between them and their use cases can help you optimize your database performance.

Clustered Index

1. Definition: A clustered index determines the physical order of data in a table. There can be only one clustered index per table because the data rows themselves can be sorted in only one order.

2. Structure: The leaf nodes of a clustered index contain the actual data pages of the table. This means that the data is stored in the order of the clustered index key.

3. Primary Key: By default, when you create a primary key constraint on a table, SQL Server creates a clustered index on that column (or columns).

4. Performance: Clustered indexes are efficient for range queries and queries that return large result sets because the data is stored in the order of the index. They are also beneficial for queries that involve sorting and grouping.

5. Example:

   CREATE CLUSTERED INDEX IX_ClusteredIndex
   ON TableName (ColumnName);

Non-Clustered Index

1. Definition: A non-clustered index does not alter the physical order of the data in the table. Instead, it creates a separate structure that points to the actual data rows.

2. Structure: The leaf nodes of a non-clustered index contain pointers (row locators) to the data pages. These pointers can be either the clustered index key (if a clustered index exists) or the row identifier (RID) if there is no clustered index.

3. Multiple Indexes: You can create multiple non-clustered indexes on a table. This allows you to optimize queries that filter or sort on different columns.

4. Performance: Non-clustered indexes are efficient for queries that search for specific values or small ranges of values. They are also useful for covering indexes, where the index includes all the columns needed by a query, thus avoiding the need to access the actual data pages.

5. Example:

   CREATE NONCLUSTERED INDEX IX_NonClusteredIndex
   ON TableName (ColumnName);
   

Key Differences

1. Physical Order: Clustered indexes determine the physical order of data in the table, while non-clustered indexes do not.
2. Number of Indexes: A table can have only one clustered index but multiple non-clustered indexes.
3. Storage: Clustered indexes store the actual data rows at the leaf level, whereas non-clustered indexes store pointers to the data rows.
4. Use Cases: Clustered indexes are best for range queries and queries that return large result sets, while non-clustered indexes are best for point queries and covering indexes.

Choosing Between Clustered and Non-Clustered Indexes

• Clustered Index: Use a clustered index on columns that are frequently used in range queries, sorting, and grouping. Common choices include primary key columns, date columns, and columns with unique values.
• Non-Clustered Index: Use non-clustered indexes on columns that are frequently used in search conditions, joins, and covering indexes. These indexes are useful for optimizing specific queries without affecting the physical order of the data.

Example Scenario

Consider a table Orders with columns OrderID, CustomerID, OrderDate, and TotalAmount.

• Clustered Index: You might create a clustered index on OrderID because it is the primary key and ensures that the data is stored in the order of OrderID.

  CREATE CLUSTERED INDEX IX_Orders_OrderID
  ON Orders (OrderID);
  

• Non-Clustered Index: You might create a non-clustered index on CustomerID to optimize queries that search for orders by customer.

  CREATE NONCLUSTERED INDEX IX_Orders_CustomerID
  ON Orders (CustomerID);
  

By understanding the differences between clustered and non-clustered indexes and their use cases, you can make informed decisions to optimize your database performance.

When to use what?