In SQL Server or similar databases, what is the difference between a clustered index and a nonclustered index?

Introduction / Context:
Indexes are essential for performance tuning in relational databases. Understanding the difference between clustered and nonclustered indexes is a core skill for developers and DBAs. A clustered index affects how data is stored on disk, while a nonclustered index is an additional structure that points to the underlying data. Interviewers frequently ask about this distinction to ensure candidates can reason about query performance and index design.

Given Data / Assumptions:
• We are working with a database engine such as SQL Server that supports clustered and nonclustered indexes. • The question asks for a conceptual difference between the two index types. • We assume familiarity with basic index concepts like key columns and row lookups. • No numeric calculation is required; this is a conceptual performance and storage question.

Concept / Approach:
A clustered index determines the physical order of rows in a table on disk. Because the data rows themselves are stored in this order, there can be only one clustered index per table. This is often built on the primary key. A nonclustered index, by contrast, is a separate structure that contains the indexed columns plus a pointer (such as a row identifier or clustered key) to the actual data rows. Tables can have multiple nonclustered indexes. Queries may use nonclustered indexes to find matching keys and then perform lookups into the clustered index or heap to retrieve full rows. A correct answer must emphasize physical ordering and the number of indexes allowed.

Step-by-Step Solution:
Step 1: Recall that for a clustered index, the table data is stored in order of the clustered key, making the index and the table share the same structure. Step 2: Note that because the table can be stored in only one physical order, there can be only one clustered index per table. Step 3: Remember that a nonclustered index stores a copy of the key values plus pointers to the actual table rows. Step 4: Recognize that many nonclustered indexes can be created on different columns to speed up various queries. Step 5: Select the option that clearly describes these storage and cardinality differences between clustered and nonclustered indexes.

Verification / Alternative check:
Consider a table with a clustered index on an identity primary key. The rows on disk are ordered by that key. If you add a nonclustered index on LastName, the database creates a separate index tree where each entry contains LastName and a pointer to the clustered key or row location. A query searching by LastName can use the nonclustered index to find matching keys, then use the pointer to read the full rows in clustered order. This example demonstrates that the clustered index defines physical order, while the nonclustered index is an auxiliary structure, matching the description in option a.

Why Other Options Are Wrong:
Option b claims that clustered indexes are always faster than nonclustered indexes in every scenario, which is not true; performance depends on query patterns and index design. Option c imposes a false restriction on data types, suggesting clustered indexes must be on character columns and nonclustered on numeric, which is incorrect. Option d ties index type to workload type (transactional versus data warehouse), which is not how clustered and nonclustered indexes are defined or used. Option e states there is no difference between clustered and nonclustered indexes, which contradicts fundamental database architecture.

Common Pitfalls:
One common pitfall is automatically using the primary key as the clustered index without considering whether another column might provide better physical locality for common queries. Another mistake is creating too many nonclustered indexes, which can slow down write operations. Developers also sometimes think that a clustered index eliminates the need for nonclustered indexes, but in practice a balanced combination is often required. Understanding the storage implications of clustered versus nonclustered indexes helps you avoid these issues and design efficient schemas.

Final Answer:
The correct choice is A clustered index defines the physical order of data in the table and there can be only one per table, whereas a nonclustered index is a separate structure that stores index keys and row pointers, and many nonclustered indexes are allowed., because it accurately captures the core differences in storage and usage between the two index types.