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
- Last Inserted id in MSSQL PHP
- Using custom codification scheme instead of GUID as Primary Key
- SQL Master Table multiple left joins with Key-Value Pair Look ups
- The schema update is terminating because data loss might occur
- Assigning Dynamic Columns via SQL?
- Database structure for edited news
- MSSQL data insertion with Python and pypyodbc - Params must be in a list, tuple
- How to search Special character (%) in SQL Server 2008
- SQL Server Pass column name as where clause parameter
- How to make SSDT Profile.xml SqlCmdVariable an empty string or optional
- How to use the result of stored procedures in the same stored procedure for other function
- SQL import only different records
- Convert rows into columns by grouping one row value
- The nested query is not supported. Operation1='UnionAll' Operation2='MultiStreamNest'
- Create Stored Procedure for VIEW
- Separate two rows in one column and display it in two columns
- How can I retrieve rows faster within a time-range, in this simple case?
- How to do insert one row from one table to another table(what has less columns)?
- How to execute a stored procedure against linked server?
- UNION between select with 0 rows and select with 20745 gives 20740
- Multiple Row_Number() Calls in a Single SQL Query
- SQL Substring issue in PIVOT statement
- searching data in a database
- SQL server return only single row or null
- sqlalchemy connect to server, with not specify database
- How to select records which don't exist in another table or have a different status?
- Update one table with the results from another
- SQL Server Compact: Left Join returns only one row FOR MOST entries
- SSIS - connection to new database
- Post build on database project
- TSQL Select column values which has multiple values in another column
- Can I get a reference to a pending transaction from a SqlConnection object?
- Grouping and sum Quarter values
- Creating a proxy instance failed - why this error on Windows server 2012, IIS8 using NHibernate 1.2 and Castle.DynamicProxy 1.1.5?
- The Integration Services component is not installed or you do not have permission to use it
- How to get an ASP.NET MVC 3.0 site to use a db other than the default SQLEXPRESS
- .NET database calls slow when using COM Interop, fast via query analyser
- How to Format SQL Query? Use Pivot?
- I want to sum time and then multiply with salary using sql
- "Not In" does not work correctly in sql
- Error message "Pivot columns must be comparable"
- MSSQL query for all records between two date range of current day
- T-SQL Restore DB with name Variables
- Insert NULL value from xml to database
- Troubleshooting MSSQL Connection from PHP
- What is the fast way to update with 100 millions+ of records?
- Breaking out yearly payments into monthly payments with month name in a 3 year period
- Why does SQL Server keep creating a DF constraint?
- SSMS query-window connection options are all disabled after a network disconnect, can't be enabled after that is corrected
- SQL - what's the best way to look up which table a column belongs to?
- Query optimization from from query execution plan
- Count Number of Columns In Hive
- Find max value from coalesce function
- MSSQL PHP cannot delete rows
- Determine a table's primary key using TSQL
- List of aggregate functions
- Redirecting site from http to https using url rewrite in IIS 8.5
- Sql Server 2005 Data Types
- Bookmark Lookups - How efficient is the lookup using a row pointer?
- MSSQL query returns no results
- When should you use full-text indexing?
- Is there a way to insert object into DB using node.js mssql module
- Running batch file which runs 2 other batch files from SQL Server job
- Script to identify who is restoring database
- SQL Stored Procedure Validation in .NET Front End (Similar to Parse Option in SSMS)
- Azure SQL: How to be notified if someone exports the database?
- SQL Server 2014 Set values to lookup table ID
- User Login with Read Access and Create/Alter View Access in SQL Server
- An error occurred while updating the entries - String or binary data would be truncated
- Inserting a value that comes outside of XML while using xQuery in stored procedure in MS SQL Server
- Stored procedure with optional parameters
- Confused by Syntax for updating a View
- EF Core: Simple query - why so slow?
- How to select and insert values in mysql using stored procedures
- Remove first leading 0 from varchar with MSSQL
- Can a dynamic table be returned by a function or stored procedure in SQL Server?
- Is SQL server the best DB for Storing and comparing images in database for a small ecommerce application
- Add separator when fetching values from XML in SQL
- How do you reference a second SQL Server
- sql-server and oracle - order by in subselect
- "Insert Into" statement causing errors due to "Parameter 7 (""): The supplied value is not a valid instance of data type float."
- How to Calculate the number of months between two dates in YYYYMM Format?
- Insert multiple rows into SQL Server based on start and end dates
- What does "numeric precision radix" mean in the SQL Server metadata?
- Laravel mssql driver support
- ASP.NET EF6 - 2 Tables & 1 Link Table
- Recursive CTE SQL (Parent Child)
- SQL Server String Concatenation with Null
- Create User To Run SQL Migration Script Azure SQL
- alternative to xp_cmdshell bcp?
- SQL server query to search and stuff multiple rows
- Create SQL Server job automatically
- OBIEE TOPN Display Function
- Group data rows by near time
- How to get max date of previous year in a dataset with a window-function in SQL Server
- How can I run a SQL query iteratively for every row in a table?
- Are statistics automatically updated when a new index is created?
- SQL: Take the last 4000 characters from text
- top 3 ranks corresponding to every partition in 3 different columns in SQL Server
- Empty string results in "Errors were detected in the command line arguments, please make sure all arguments are set correctly"
- Can a array list of C# be used to populate SSIS object variable?
- Why use JOIN rather than inner queries
- How to update xml column node value with another column new value at same update query?
- SQL Concat rows to variable
- TSQL populate table with future days (different date formats)
- How to query xml value inside a xml column in SQL server
- Split date into intervals
- How is a guid actually stored and sorted/compared in SQL Server?
- SQL Server 2012 Change Data Capture Error 14234
- How to use Dense Rank and automatically generate dates
- Connecting to SharePoint from SSIS using OData Source
- Returning a count of records in a view from a T-SQL function
- SQL Server - current user name
- Merge: when not matched by source - update rows
- Storing SSIS Execution Results in to a table
- Creating foreign key in SQL Server Management Studio
- Headless build .sqlproj file on TFS build server
- Max int value in t-SQL
- How to do Sql server procedure using parameters like where @para1 = @para2
- How to call a stored procedure using ADO.NET?
- SQL Server : conditional concatenation
- Concatenate nested JSON in SQL Server
- Moving from text to varchar(MAX): Are there any troubles to expect with MS Access?
- SQL Server Find the date in joining order
- EF - database connect - expected to use SQL Server Authentication but trying (and failing) with Windows Authentication
- How do I find and decouple entities from a certificate when upgrading MS-SQLServer editions?
- How to find the wrong dates after an 'out-of-range value' error in SQL Server?
- ASP.NET Session Performance
- Concatenating Values into a row
- Where does a Remote SSIS package actually run
- How to identify hardcoded database name - SQL Server 2008 r2
- Database Connectivity using Linked Server. (Numeric Values)
- BulkCopy WriteToServer() Inserts Into Wrong Column
- The best database for a C++ and java applications
- Copying just the data from one Database to another
- How to update/Change the date format to MM/dd/yyyy from dd/MM/yyyy format in sql server?
- How to use ASPNETDB on shared hosting?
- How does this create view code work?
- When does ODBC closes a connection after doing a SQLFreeHandle?
- How do I change "Database default locations" for LocalDB in SQL Server Management Studio?
- SQL subtracting data based on another column
- Find element or attribute value anywhere in XML
- How to simplify a query between Many to Many Relationship
- How can paging be supported when the sort order has to be culture specific?
- SQL Server job output to Azure blob storage
- PHP SQL Server character inconsistencies
- Match comma separated values in column
- EF 4.1 trying to set the value of a computed column
- SQL Server: How to get a rolling sum over 3 days for different customers within same table
- Average Response time of items