Difference Between Clustered and Non-Clustered Indexes in SQL Server

Q

What Is the Difference Between Clustered and Non-Clustered Indexes in SQL Server?

✍: FYIcenter.com

A

SQL Server 2005 supports two types of indexes: clustered index and non-clustered index. Here are the main differences between them:

  • One table can only have only one clustered index.
  • One table can only have many non-clustered index.
  • A clustered index requires no separate storage than the table storage. It forces the rows to be stored sorted on the index key.
  • A non-clustered index requires separate storage than the table storage to store the index information.
  • A table with a clustered index is called clustered table. Its rows are stored in a B-Tree structure sorted.
  • A table without any clustered indexes is called non-clustered table. Its rows are stored in heap structure unsorted.
  • The default index created as part of the primary key column is a clustered index.

 

Understanding and Managing Indexes in SQL Server

⇒⇒SQL Server Database Tutorials

2016-11-13, 731👍, 0💬