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.
- How does database indexing work with real time example?
- Clustered and Non clustered index with real example
- Difference between unique and non unique index
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.
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.
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.
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.
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
- Linq to SQL Group by and Sum in Select | Linq To SQL Group By and Sum
- How send an HTTP POST request to a server from Excel using VBA?
- What is Difference between where and having clauses in SQL server
- How to Use EXCEPT Operator with an example in Sql
- How to use merge statement in the SQL server
- How to write Re-runnable SQL server query
- How to create Cursor in sql server with example
- How to generate random data in sql server for performance testing
- How to write subquery in select statement in Sql
- How to Log record changes in SQL server in an audit table
- Logon Trigger Example to Restrict Access in Sql Server
- DDL Triggers In Sql Server with Example | Database and Server Scoped Triggers
- How to achieve ACID properties with Example in Sql Server
- What is transaction in sql server with example
- Instead Of Delete Trigger In Sql Server With Example
- Instead Of Update Trigger In Sql Server With Example
- Instead Of Triggers in Sql server With Example
- After Update Trigger in Sql Server With Example
- Triggers in SQL server with real-time example
- C# -Saving a base64 string as an image into a folder on server in Web Api
- what are the advantages and disadvantages of indexes-Sql Server?
- Sql Server- Difference between unique and non unique index
- Temporary Tables in sql server with real time example
- Clustered and Non clustered index in sql server with real example
- How does Recursive CTE works in Sql server?
- How to update table using CTE in sql server
- How to use group by in SQL Server query?
- Sql Server pivot rows to columns
- What is CTE in sql server with example
- SQL Server – -Simple way to transpose rows into columns
- What is Sql Joins? With Realtime Examples
- Difference between Inner Join ,Left Join and Full Join-Sql
- How to get Employee manager hierarchy in Sql ?
- How to create Inline table-valued functions in SQL server
- Scalar User-defined functions In sql server-Step by Step
- [Solved]-How To Update a Table using JOIN in SQL Server?
- Create Stored procedure with Output and Input parameters in SQL
- [Solved]-Best Sql server query with pagination and count
- [Solved]- find records from one table which don’t exist in another Sql
- [Solved]-How to concatenate text from multiple rows into a single text string in SQL server?