in Sql by (23.9k points)
What is the difference between clustered and non-clustered indexes in SQL?

1 Answer

0 votes
by (23.9k points)

Indexing is a method to get the requested data very fast. There are mainly two types of indexes in SQL, clustered index and non-clustered index. The differences between these two indexes are very important from an SQL performance perspective. The following comparison chart explains their main differences:

Clustered Index

  • A clustered index is a table or view where the data for the rows are stored. In a relational database, if the table column contains a primary key, MySQL automatically creates a clustered index named PRIMARY.
  • Clustered indexes store the data information and the data itself.
  • There can only be one clustered index per table.
  • A clustered index determines how data is stored physically in the table. Therefore, reading from a clustered index is faster. It creates a logical ordering of data rows and uses pointers for accessing the physical data files. Therefore, reading from a clustered index is slower.
  • A clustered index always contains an index id of 0.

  • Non-clustered Index

  • The indexes other than PRIMARY indexes (clustered indexes) are called non-clustered indexes. It has a structure separate from the data row. The non-clustered indexes are also known as secondary indexes.
  • A non-clustered index always contains an index id>0.
  • There can be one or more non-clustered indexes in a table.
  • Non-clustered indexes stores only the information, and then it will refer you to the data stored in clustered data.

Related questions

0 votes
0 votes
asked Dec 15, 2020 in Sql by SakshiSharma (32.2k points)
0 votes
asked Nov 7, 2021 in Sql by rajeshsharma (23.9k points)
+2 votes
asked Jan 15, 2022 in Sql by GeorgeBell (5.1k points)
0 votes
asked Nov 27, 2020 in Sql by rajeshsharma (23.9k points)
...