An index in SQL may be defined as a structure linked with a particular table and is stored on the disk, mainly created to enhance the speed of the retrieval of data. The key for an index in SQL is created from one or more than one column present in a particular table or even view.
There are two types of indexes used in SQL and they are as follows:
1. Clustered Indexes: This type of Indexes are used to store and sort out the rows of data in a specific table or view basically according to their key values. Only one clustered index is allowed in SQL for one precise table because, the sorting out of the rows of data can be done in only one particular order. If a table has a clustered index linked to it in SQL then this table is known as Clustered Table. In case, of the absence of a clustered index the rows of data or data rows get stored in heap which is a totally unordered structure.
2. Non-Clustered Indexes: The second type of Index in SQL is known as Non-Clustered index. This type of Index contains the key values known as non clustered index key values and each of this key value has a pointer directed towards the data row containing that particular key value. This pointer which is there in the row of a non clustered index is known as a row locator. The basic structure of this row locator is based on the way the pages are stored. If the data pages are stored in a heap then the row locator will be the pointer to the specific row but if these data pages are stored in a clustered table then this row locator will be the clustered index key.
Read: More Interview Questions
As we have understood the basic concept of clustered indexes and non-clustered indexes in SQL, let’s go through the basic difference between these two types of indexes:
1. The basic difference between a clustered index and a non clustered index in SQL is that, one table can have exactly one clustered index associated with it but a single table can be associated with more than one non clustered indexes and the number can be as large as 250.
2. Another important difference between these clustered and non-clustered indexes is that in case of databases like SQL server the clustered index is made or created on PRIMARY KEY constraint whereas when it comes to non clustered index, it can be created on any key or in other words this type of index is not declared while specifying the PRIMARY KEY Constraints.
3. A clustered Index basically contains the data that means the rows in there every leaf node, but in case of the non clustered index, it contains the pointer to the specific data in the leaf node which adds one more step to the whole process of retrieving the data. Also, the clustered Index sort all the rows of data physically but this type of physical sorting of all the data is absent in case of non-clustered rows.
4. When it comes to clustered index, no separate storage other than the table storage is required to store the rows as in this case the rows are sorted and stored on the index key only. In case of non-clustered index, it requires different and separate storage space than of table.