Clustered index in a database can be defined as the order in which rows present in a table are stored. Clustered indexes are used to accelerate the query searches in the database. It is easier to identify the data if rows are arranged in a proper manner.
Example for Clustered Index
Consider that we have an employee table which contains field named “employee_name”. If clustered index is used in this particular field; every rows stored in the disk will be physically sorted. So whenever a search query for employee id is requested it will be processed using clustered index. The search process will be quicker when compared to the normal process because the order of the actual data stored in the disk similar to the order of rows present in a table. Clustered Indexes are stored in the “tree data structure” so every leaf node in the tree will correspond to the actual data in the disk.
Why Clustered Indexes are used?
Consider that we have two tables which contain employee details and department details. A simple draft for fields present in the table is given below.
Let us assume that we have a manager who is also an employee. This particular manager may take care of several departments in the company. The clustered index created for emp_name will contain all the dept_name the works in. If you have a manager named Smith who works in manufacturing and engineering departments; the details of Smith will contain entries about manufacturing and engineering.
Advantages of Using Clustered Index
- Clustered index accelerate query search.
- Data retrieval can be accelerated using clustered index.
- Rows in the disk are arranged in proper manner.
- If a index has several entries, clustered index arranges it in proper manner.
- Unique indexes are not required to use clustered index.
Disadvantages of Clustered Index
- If values present in a clustered index column are updated then it forces the entire row to be moved.
- The rows present in the clustered index column are updated for every change in the value.
- Every row added in clustered index are considered as DELETE and UPDATE operation rather than INSERT.
- Clustered index are usually created by using primary and foreign keys.
- Only one clustered index column is allowed in a database table while multiple non-clustered index columns are allowed. The clustered index column in a table is restricted to one because rows can follow only one reference value to arrange itself in the disk.
Read: More Interview Questions
Difference between Clustered and Non-clustered Index
- The order in which the rows are stored in a disk is determined by clustered index. The row level data present in the columns are stored in the leaf nodes of the index. Nonclustered indexes will have no effect on the order of the rows stored in the table.
- If you want to access a certain group of data frequently then clustered index is the best option.
- Nonclustered indexes cannot be used to accelerate search or data retrieval in a table.
- Rows present in clustered indexes are resorted every time a change made in index column.
- The performance of Non clustered index is not affected much because it does not require any row sorting.
- Only one clustered index are allowed in a table while multiple non clustered indexes are permitted.
- The value and pointer of actual row are stored along with non clustered indexes. No pointers required in clustered index because actual row order is maintained.
- Row level data is stored in the leaf nodes of clustered index.