Improve the performance by using Indexing in SQL

Index in SQL

have you ever faced slowness while fetching the records in the SQL. If yes, then you are at the right place 😉. First let’s understand the reason behind it.

So at the beginning of any project each table has small amounts of data and when we retrieve the data we don’t feel slowness. But when the project is very big and most of the tables store thousands of records then query execution will be slow if the table is not indexed. Because of indexing is not used in the table SQL will visit all the rows and and will retrieve the matching records.

Index:-

Index in SQL is same like index of any book where the topic and the page number is given so that we don’t have to search all the pages to read about that particular topic.

Types Of Index :-

There are two types of index in SQL i.e. Clustered Index and Non-Clustered Index.

1) Clustered Index:-

Clustered index defines the order in which data will be stored in table. Only one clustered index allowed per table as it defines the order of the table storage. Primary Key constraint automatically creates one clustered in for the table on that particular column.

Example:-

CREATE CLUSTERED INDEX studentIDX ON STUDENTS(StudentID DESC);

2) Non-Clustered Index:-

Non-Clustered Index does not define the order of the data. It can have multiple columns in it. SQL stores the Non-clustered indexes in the table format in the pages where last column points to the page number of the clustered index if it is present else it points directly to the table page number.

Now let’s check how performance is getting improved by using Indexing on table.

Without Indexing:-

If we don’t use any index in the table then SQL will visit all the records row by row hence execution time will get increased if records are in the thousands or more than that.

As in given Screenshot it is visible that in “Number of Rows Read” parameter value is same as the number of records present in the table.

With Clustered Index:-

If we use clustered index then execution time will get decreased.

As in the given Screenshot it is visible that in “Number of Rows Read” parameter value is 1 and execution time has also decrease.

Bhagavad Gita Verse:-

Knowing this truth, even seekers of liberation in ancient times performed actions. Therefore, following the footsteps of those ancient sages, you too should perform your duty.