Saturday, October 20, 2012

What is difference between Clustered and Nonclustered Indexes in SQL - Database

Clustered vs Nonclustered Indexes in SQL
Difference between Clustered and Nonclustered index in relational database is one of the most popular SQL interview questions almost as popular as  difference between truncate and deleteprimary key vs unique key and correlated vs noncorrelated subqueries. Indexes are very important concept, it makes your queries to run fast and if you compare a SELECT query which uses an indexed column to one who doesn't you will see big difference in performance. There can be two kinds on indexes in relational database Clustered and Nonclustered indexes. Clustered index determines physical sorting order of rows in a table similar to entries on yellow pages which are sorted on alphabetical order. Suppose you have a table Employee, which contains emp_id as primary key than clustered index which is created on primary key will sort the Employee table as per emp_id. That was a brief introduction of What is clustered index in SQL. On other hand Nonclustered index involves one extra step which point to physical location of record. In this SQL Interview question we will see some more difference between clustered and nonclustered index in point format.

Difference between Clustered and Nonclustered Indexes in SQL

What is difference between Clustered and Nonclustered index in SQL
In last section we have seen What is Clustered and Nonclustered index and How they resolve physical location of record. Let's see some more difference between them to answer this SQL interview question :

1) Clustered Index physically sort all rows while Nonclustered Index doesn't.

2) In SQL one table can only have one Clustered  Index but there is no such restriction on NonClustered Index.

3) In many relational databases Clustered Index is automatically created on primary key column.



That's all on difference between Clustered vs Nonclustered Index in SQL. Index are very critical to query performance but too many index can actually harm performance of INSERT and UPDATE queries because these requires rearrangement of indexes which can be time consuming based upon size of table or number of records on table. Always create Indexes on columns which is frequently used in SELECT query because there is huge performance difference between indexed and non indexed column in SQL queries.

Other database and SQL articles you may like
What is ACID properties of database transaction?
What is mysqludump utility of MySQL database?
10 frequently used MySQL commands for beginners
Top 15 Linux command interview questions with answers
Top 10 Java design pattern interview questions with answers

3 comments:

  1. One of the key thing to remember about Clustered Index is that it organize data in cluster, which helps to quickly scan and retrieve data in a range. also since clustered index decides physical order of data, there can be only one clustered index in a table. All other non clustered index, uses a pointer, which points to clustered index. That's why in index tree, in case of clustered index, leaf contains data, but in case of nonclustered index, it contain pointer to data.

    ReplyDelete
  2. Always remember indexes in SQL are double edged sword, though index improves SELECT query performance (retrieval), it affects insert and update query performance, because index needs to be updated, rearranged every time.

    ReplyDelete
  3. NONCluster index create separate physical structure for searching

    ReplyDelete

Java67 Headline Animator