SQL queries on Clustered and Non-Clustered Indexes

Indexing:

The process of indexing makes the defined table return the required data more quickly. The SQL server must search the entire table to find your data if there are no indexes. Similar to how you would search for material in a book by looking at the index page, the SQL server will index. Similar to how a table's index enables us to find precise data without having to scan the entire database. In SQL, there are two categories of indexing. Indexing helps in the database performance by reducing the number of disc accesses when a query is executing. It is a data structure method used to locate and access data in a database rapidly.

Clustered Indexing:

The type of indexing that creates a physical order for sorting data is called a Clustered Index. If your Stud_details table has ID_NO as its primary key, a Clustered Index that you constructed yourself would sort the Stud_details data according to ID_NO. Similar to a dictionary, a Clustered Index has no distinct index page and is sorted alphabetically. For Clustered Indexing, ordered files are used. Any column which is not a key is used to define the order of the file. Many times non-primary columns are used to produce indexes that are not unique for all the rows. In that condition, we usually combine two or more columns to make a unique value which will help in accessing the entries fastly. The Clustering Index is the name of this technique. To put it simply, indexes are made for groupings of records that share common features.

SQL Code:

Output:

The primary key must be deleted before the previous index can be deleted if we want to establish a Clustered Index on a different column. Keep in mind that when a column is designated as a primary key, that column becomes the table's Clustered Index. We must first remove the preceding column, as seen below before we can create any additional column, the Clustered Index.

Steps:

Note: there can be a maximum of one Clustered Index possible in a table.

Non-Clustered Indexing:

Non-Clustered Indexes are index structures that rearrange one or more chosen columns independently of the data kept in a table. To enhance the performance of commonly used queries not handled by a Clustered Index, the Non-Clustered Index was developed. It's similar to a textbook in that the index page is made independently at the start of the book. In other words, a Non-Clustered Index simply provides us with a collection of virtual links or connections to the real storage location of the data. When we store the data physically, its index does not matter at that time. Data is present in leaf nodes. Compared to the Clustered Index, it takes longer since more work must be done in order to retrieve the data by following the pointer further. When an index is clustered, data is directly in front of the index.

SQL Code:

Output:

Note: We can create more than one Non-Clustered Index in a table using the following syntax:

Difference between Clustered and Non-Clustered Indexing:

• The Non-Clustered Index is slower, while the clustered index provides faster data access.
• When an index is clustered, there is no distinct index storage; nevertheless, when an index is not clustered, there is a separate index storage.
• There can only be one Clustered Index but one or more Non-Clustered Indexes in a table.