Hello, welcome to Appsloveworld technologies, In this session, we learn about what are the advantages and disadvantages of indexes, the types of queries that can actually benefit from indexes.

Now, before continuing with the post, I strongly recommend reading below posts of this series.

Advantages of indexes

So if you look at the table TblUsers that we have here, which has got its Name, Email, Gender, gender RewardsPoint columns, and on this table, the ID column is the primary key column.

And we know that when we have a primary constraint on a table by default, it creates a unique clustered index on that column. So on the ID column, we have a clustered index. The data is arranged in the ascending order of the column.

1

But if you look at the RewardsPoint column, this is not arranged in any order because the data is already arranged in the ascending order of the ID column.

Now let’s create a Non-Clustered Index on RewardsPoint Column

Create NonClustered Index IX_TblUsers_RewardsPoint
On TblUsers (RewardsPoint Asc)

When you create an index on RewardsPoint Column ,internally it will look like the below image.
2

Now, you can see an index on the RewardsPoint column,And if you look at the RewardsPoint , they are arranged in ascending

For example, if I want to find out who is the user who has got 127.00 Point, you will look at the row with id=12 and there is a rowaddress . And as we pick up the rowaddress and get the row, which has got 127.00, which is Aakankshi Gupta record directly.

3

Now, let’s say somebody writes a query like Select * from TblUsers where RewardsPoint > 100 and RewardsPoint < 500. if you look at query, I want all the users whose RewardsPoint falls between 100 and 500.

Obviously this query can benefit from the index on the RewardsPoint column because RewardsPoint’s are in ascending order so the database engine can quickly narrow down the rows that fit into this category.

So if it starts at 100, no, it doesn’t fall into this category, but 109.00 Falls into this category. So it starts from there and takes everything until 500. And then it has the row addresses. So it goes to the actual table and retrieves all the other columns that we are requesting in the select clause.

So select statements with a where clause can benefit from the right indexes. Even delete and update statements can benefit.

if I want to delete a particular record, let’s say, for example, I want to delete users whose RewardsPoint is 127.

delete from TblUsers where RewardsPoint=127

Now assuming that I have a million records in the stable. Now, obviously, I have to find all the users who RewardsPoint is 127. I’ll have to look at every record instead of that. If there is an index on the RewardsPoint column, rows with 127 will be present in consecutive rows.

So they will be adjacent to each other and it is easy to pick those rows addresses and then go to the base table and delete these rows.

And along the same lines, update queries can also benefit. because we are updating the RewardsPoint of all users whose RewardsPoint is currently 127.

Group by clause can benefit from the index

Now what we are trying to do here, we are grouping or we are counting the total number of users at each RewardsPoint. For example, I mean, the total number of users who have got 127 RewardsPoint or 100 as their RewardsPoint.

Select RewardsPoint, COUNT(RewardsPoint) as Total
from TblUsers
Group By RewardsPoint

So basically we have grouping users by RewardsPoint . we know that in the index, users with similar RewardsPoint appear next to each other. And they will appear consecutively in this index, so counting them will be relatively easy from the beginning till the end.

So it will Scan the index find the rows which have similar RewardsPoint and count them and simply return that results.

Disadvantages of indexes

Now, we spoke about the advantages basically indexes help queries to find data quickly. So what are the downsides?

The downside, obviously, additional disk space.

Clustered indexes don’t require any additional storage. Every non clustered index required additional space as it is stored separately from the table. The amount of space required will depend on the size of the table and the number and type of columns that we are using in the index.

Now, as non clustered indexes are store separately from the base table,so it require additional disk storage. But these days, disk storage is less costly. we can actually trade that for best application performance.

if we create a composite index, then there are more number of columns in the index and the amount of space required will be more.

Insert Update and delete statements can also become slow

Now in the above, we said that, if we have deleted and update records, obviously they can benefit from an index.

But here we are saying insert update and delete can become slow. That’s because, in order to deleted or updated records, we need to locate that, it can be reduced.

But then if the tables are huge and if there are too many indexes, when you delete OR update records, then the index needs to be updated as well.

If you look at this, for example, if you take a look at TblUsers in this index, let’s say, for example, I am deleting a user with RewardsPoint equal to 127 from the TblUsers.

3
Now, when that record is deleted from the table, it also has to be deleted from the index.so if there are ten other indexes that are referring his name OR Gender, obviously, all those indexes need to be updated as well.

so that’s why if there are too many indexes, they can actually affect insert, update and delete statements.

so In DML statements, data manipulation statements, the data and all the indexes also need to be updated.

Sql Indexes can help to find and locate the rows that we want to delete from the table. But too many indexes to update can actually heart the performance of data modifications. That’s why index tuning is a very important task. You have to choose the right number and type of indexes.

The post what are the advantages and disadvantages of indexes-Sql Server? appeared first on Software Development | Programming Tutorials.



Read More Articles