Indexes are used to fasten the query process in the SQL server that results in high performance. They are quite similar to the textbook indexes. In a textbook, to open a particular chapter or topic, you first go to the index, and find the name of chapter or topic then jump to the desired page directly. Without indexes in the textbook, finding a specific topic in the textbook would be very slow.
The same concept is applied to the database indexes as well. If there are no indexes in the SQL then DBMS has to go through all the records in a table to retrieve the desired results. In this case, the process of scanning the desired records is extremely slow. On the other hand, when you create indexes, the DBMS goes to that index first and retrieves the corresponding record directly without wasting any time into other things.
There are two common types of indexes in the SQL server, these are the Clustered Index and the non-clustered index.
Every table in the database has a natural order. If the order is random and it is not defined explicitly then the table is known as the heap here. Except for the few cases, heaps are not considered as a good programming practice because the SQL server has no meta-knowledge about how data is stored within a heap. If there is not a random heap, it means table data is stored in some order and the physical storage of data is defined by the clustered indexes in SQL. Every table can have only one clustered index because data can be stored in a single order only. It signifies that data in a table stored physically on the disk cannot have more than one order.
Example of Clustered Indexes
Take the example of “Student_info” table to understand the practical aspects of clustered indexing in the SQL server. This table contains “Roll_No” as the primary key and the clustered index is self-created here on the primary key that will sort the “student_info” table as per the “Roll_No”. In other words, clustered indexing works as the dictionary where sorting order is alphabetical and there is no need for defining any additional index page.
Input: CREATE TABLE Student_info ( ROLL_NO int(10) primary key, NAME varchar(20), DEPARTMENT varchar(20), ); insert into Student_info values(1410110405, 'H Agarwal', 'CSE') insert into Student_info values(1410110404, 'S Samadder', 'CSE') insert into Student_info values(1410110403, 'MD Irfan', 'CSE') SELECT * FROM Student_info
Output for this query will be:
If you want to define clustered indexes for other columns then first you should remove the primary key then you can remove the first index too. Keep in mind that defining a column as the primary key will make that column the clustered index of that table. To set any other column as the clustered index, we should remove the previous one with the following procedure:
//Drop index drop index table_name.index_name //Create Clustered index index create Clustered index IX_table_name_column_name on table_name (column_name ASC)
What are the benefits of a clustered index?
What are nonclustered indexes in the SQL Server?
A nonclustered index contains the ordered data for the columns specified in that index with pointers that will guide where to find the rest of the data from that row. Unlike clustered indexes, nonclustered indexes are a two-step process where you should first the value of interest then go and look up the rest of the data of that row where it actually exists on the disk. The non-clustered index is an index structure separate from the data stored in the table that renders one or more selected columns. The non-clustered index is created to improve the performance of frequently used queries that are not managed well by clustered indexes. It is like a notebook where the index page is put separately at the beginning of the book.
Example of nonclustered indexes:
Input: CREATE TABLE Student_info ( ROLL_NO int(10), NAME varchar(20), DEPARTMENT varchar(20), ); insert into Student_info values(1410110405, 'H Agarwal', 'CSE') insert into Student_info values(1410110404, 'S Samadder', 'CSE') insert into Student_info values(1410110403, 'MD Irfan', 'CSE') SELECT * FROM Student_info
Output for this query will be:
Keep in mind that it is possible creating multiple nonclustered indexes for the database tables while the clustered index is exactly one for every database table. Here is how to define nonclustered indexes in the SQL server –
Input: //Create Non-Clustered index create NonClustered index IX_table_name_column_name on table_name (column_name ASC)
Input: create NonClustered index IX_Student_info_NAME on Student_info (NAME ASC) Output:
What are the benefits of a nonclustered index?
Example Usage Scenarios
If there is an OLTP table that is used for transactional reads and writing new rows. The primary key is the major identifier here. So, what type of index you should use for the primary key here?
It is recommended using the clustered index here when the primary key is the major identifier for the table. If you store the data in the same format as arranged by the primary key then SQL can read data very quickly. If there is a query that you want to return most or all the columns from a table then what type of index is suitable in this case?
The clustered index is suitable here because all columns are stored in the same order as sorted by the primary key in the clustered indexing process.
There is a table whose values changes constantly and those updated values are used in Join and Where clauses. So, which type of indexing suits most here?
Nonclustered indexes are more suitable for tables whose values change frequently.
From the discussion, it is clear how clustered and nonclustered indexes are different.
For more information on SQL indexing and its usage, join the SQL certification course online at JanBask Training and master the database concepts thoroughly.
JanBask Training is a leading Global Online Training Provider through Live Sessions. The Live classes provide a blended approach of hands on experience along with theoretical knowledge which is driven by certified professionals.
Receive Latest Materials and Offers on SQL Server Course