What is the difference between clustered and non-clustered indexes?

Clustered and non-clustered indexes are methods of organizing data in a database to speed up searches.

• Clustered Index:

  • Defines the physical order of rows in a table.
  • There can be only one clustered index per table, as data can only be sorted by one key.
  • Searching by a clustered index is usually faster because data is stored sequentially.
  • Example: In a table with a clustered index on the ID column, rows are physically sorted by ID.

• Non-Clustered Index:

  • Stores a separate data structure (e.g., B-tree) with keys and pointers to table rows.
  • A table can have multiple non-clustered indexes.
  • Does not change the physical order of rows in the table.
  • Used for quick searches on other columns.

In C# when working with databases (for example, through Entity Framework), you can create these indexes to optimize queries. The clustered index affects the physical storage of data, while the non-clustered index affects an additional structure for quick access.