Image courtesy of Grant Cochrane / FreeDigitalPhotos.net
SQL : Clustered and NonClustered Index
We read about SQL indexes in our previous post, We will dive into the topic and learn the different types of indexing possible in SQL.
We have clustered and non-clustered index in SQL, let’s see the difference, their operation and advantages each one have.
We have a basic understanding that indexes are created on columns and organized into B-Tree structure for faster retrieval of data so both clustered and non-clustered index follows the same.
But the difference is,
1. Clustered indexes physically [actual bits on the disk] sorts and stores the data rows in the table based on the index values. means,the rows are stored physically on the disk in the same order as the index
2. A clustered index determines the order so there can be only one clustered index per table.
3. As a rule, this should be applied to the most common column which is always a part of WHERE clauses for queries.
4. Any data operation in the Table (like inserts, updates and deletes) reorders the index as clustered follows a physical sort and storing.
5. All the data operation will be slower. This is an overhead or a disadvantage.
6. As a best practice, the clustered index should be on a column which is unique and increasing such as an identity column.
7. Columns with the data type of TEXT, NTEXT and IMAGE are not included.
1. Non-Clustered index follows logical ordering of data rows. means, rows of data might not follow the way the index is ordered or sorted.
2. The index values will not point to the data rows but will have the address of the data rows.
3. Try to understand it more clearly, if we are looking for a value in non-clustered index then we will not get the data row directly like in the case of clustered, we will get the row locator and with the help of that we can get the physical pointer (corresponding clustered index value) of the data row.
4. 999 non-clustered indexes can be created.
5. Can contain upto 1023 included columns.
6. Columns with the data type of TEXT, NTEXT and IMAGE are not included.
What is the relation between keys and indexes?
When we assign PRIMARY KEY / UNIQUE key on table columns, Indexes are automatically created.
Let’s create a Employee table:
Create Table Employee ( EmpId int, EmpName varchar(100) , Salary int, )
Right now, there is no index. Apply index on EmpId.
CREATE UNIQUE INDEX idxEmpId ON Employee (EmpId);
If there is already a clustered index on Table then a unique non-clustered index gets created else a unique clustered index gets created.
While defining the index, you can explicitly mention non-clustered to create a non-clustered index but before that a clustered should exist.
CREATE NONCLUSTERED INDEX idxEmpId ON Employee (EmpId);
Always find a balance between indexes because
1. More number of indexes are a spoilsport for inserts/updates.
2. Less number of indexes will hit the performance.