In SQL Server, what is the main difference between a clustered index and a non clustered index in terms of how data is stored and accessed on disk?

Introduction / Context:
Index design has a major impact on SQL Server performance. Understanding the difference between clustered and non clustered indexes is a fundamental database concept. Interviewers often ask this question to assess whether a candidate can reason about storage structures, query plans, and how to choose appropriate indexing strategies for tables in real applications.

Given Data / Assumptions:
We are using Microsoft SQL Server, but similar concepts exist in other relational databases.Tables can have at most one clustered index but multiple non clustered indexes.Indexes are used to speed up searches, joins, and sorting operations.The focus is on physical ordering versus separate pointer based structures.

Concept / Approach:
A clustered index determines the physical order of rows in the table. In SQL Server, a clustered index table stores data rows in the leaf level of the index, so the table itself is organized according to the clustered key. This is why there can be only one clustered index per table. A non clustered index, on the other hand, is a separate B tree structure that contains key values and pointers to the actual data rows. The pointer may be a row identifier or the clustered key, depending on whether the table is a heap or clustered. Non clustered indexes do not change the physical order of the data itself.

Step-by-Step Solution:
Step 1: Recall that a clustered index defines how rows are physically stored on disk.Step 2: Understand that a non clustered index contains key values and references to the underlying data storage.Step 3: Recognize that this is the key difference tested in interview questions.Step 4: Review the options and choose the one that states this clearly without adding incorrect restrictions.Step 5: Option A matches this explanation exactly, so it is the correct answer.

Verification / Alternative check:
You can verify this by examining execution plans and storage properties of tables with different index configurations. For a table with a clustered index, the index leaf level contains data pages. For a table with a non clustered index, the leaf level contains key entries and pointers that direct SQL Server to find the actual rows. Books and documentation often show diagrams that differentiate these structures, reinforcing the explanation in option A.

Why Other Options Are Wrong:
Option B suggests that indexes are restricted by data type, which is not true; both clustered and non clustered indexes can be created on many data types. Option C confuses indexing with backup and restore operations, which are separate subsystems. Option D claims that clustered indexes are always faster, but in reality performance depends on the query pattern and index design. Option E says that clustered indexes are created automatically on every column and non clustered indexes cannot be created manually, which misrepresents how indexing works.

Common Pitfalls:
One pitfall is creating clustered indexes on columns that are frequently updated and not used for range queries, which can cause fragmentation and overhead. Another mistake is adding many non clustered indexes without considering maintenance cost and write performance. Some learners also confuse heaps, which are tables without clustered indexes, with non clustered indexes. Understanding the physical order of data and the pointer based nature of non clustered indexes helps design efficient schemas.

Final Answer:
The correct answer is: A clustered index defines the physical order of rows in the table, while a non clustered index stores a separate structure that points to the data rows.