SQL Interview Questions

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. When a table has a clustered index, its data rows are stored in the order of the clustered index key.

Characteristics:

1. Physical Ordering: The rows of the table are stored on disk in the order of the clustered index.
2. Single Per Table: There can be only one clustered index per table.
3. Primary Key: By default, the primary key of a table is implemented as a clustered index.
4. Faster Retrieval: Accessing data via a clustered index is generally faster because the data is in the correct order and often requires fewer disk I/O operations.

Example:

CREATE TABLE Employees (
    EmployeeID int NOT NULL,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    PRIMARY KEY (EmployeeID) -- This creates a clustered index on EmployeeID
);

Non-Clustered Index

A non-clustered index does not affect the physical order of the data in the table. Instead, it creates a separate structure within the database that stores pointers to the physical data rows. There can be multiple non-clustered indexes per table, providing multiple paths to access the data.

Characteristics:

1. Logical Ordering: The rows of the table are not sorted in the order of the non-clustered index.
2. Multiple Per Table: There can be multiple non-clustered indexes on a table.
3. Separate Storage: The index contains pointers to the actual data rows in the table.
4. Flexible Queries: Non-clustered indexes can improve the performance of queries that do not access the data in the clustered index order.

Example:

CREATE NONCLUSTERED INDEX idx_LastName
ON Employees (LastName);

Differences Between Clustered and Non-Clustered Indexes

1. Physical vs. Logical Ordering:

   • Clustered Index: Sorts and stores the data rows in the table based on the index key. The physical order of the rows matches the index order.
   • Non-Clustered Index: Creates a separate structure that holds the index key values and pointers to the actual data rows. The physical order of the data rows is unaffected.

2. Number of Indexes:

   • Clustered Index: Only one per table.
   • Non-Clustered Index: Multiple non-clustered indexes can be created on a single table.

3. Storage:

   • Clustered Index: The data rows themselves are stored in the index.
   • Non-Clustered Index: The index contains pointers to the data rows rather than the data rows themselves.

4. Use Cases:

   • Clustered Index: Ideal for columns that are frequently used in range queries and where you want the data to be quickly retrievable in a sorted order, such as primary keys.
   • Non-Clustered Index: Ideal for columns used in searches that do not require the data to be sorted in the order of the clustered index, such as foreign keys or frequently searched columns.

Conclusion

Clustered and non-clustered indexes are essential tools in SQL databases to optimize query performance. A clustered index sorts and stores the data rows in the table based on the index key, whereas a non-clustered index creates a separate structure to hold pointers to the actual data rows. Understanding the differences between these two types of indexes helps in designing efficient database schemas and improving query performance.