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
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
- Why does SQL Server use "Binn" directory instead of "Bin"
- How can I force a Snapshot Isolation failure of 3960
- sql stored procedure paramater single quotes
- join with pivot table
- Last executed queries on SQL Azure Database (Basic tier)
- "Renaming" a database role
- Failure to execute msdb.dbo.sp_send_dbmail
- Auto-incrementing a non-identity field
- SQL Insert into multiple rows in Table 1 from Table 2 with additional information from web form C#
- Split values into separate rows
- Using bash script to connect to remote SQL Server
- Create a table without knowing its columns in SQL
- Create a sql variable and query other tables
- Non-Unicode CSV Export from SQL Server Report Server
- Override file while backup database
- How to display user databases only in sqlserver?
- On insert, output columns that wern't inserted but in joined tables
- max date for multiple id using different tables
- Serial number auto generation
- How to find rows in a column that start with a specific name?
- How to update newly added column in mysql
- Performance tuning an update statement in SQL SERVER
- Sql Server query for tree path of one item
- Make SQL Server connection work on any computer
- SQL - Combine Multiple Rows of Data to One Row and Column
- CTE keeps saying Type dont match between anchor and the recursive part
- How to get all database names in one linked server?
- SQL Server - DECLARE SET is taking too much time to execute than hardcoding the parameters in where condition
- Send message to Azure ServiceBus from SQL
- SQL string or binary data would be truncated , column name
- Inserting to table having issue - Explicit value must be specified for identity column in table
- Send notification on Time/Date from database
- SQL Server Allocation of amount in bill
- How to group by for different conditions
- Convert GETDATE() to YYYYMMDDhhmmss as derived column
- How to split string in sql?
- Could not able to retrieve distinct rows from a Column based on the condition of another column in SQL server
- Get a count of distinct rows that does not include a value in any row
- Results returned from a view using linked server may vary?
- Does an nvarchar always store each character in two bytes?
- ASP.NET core Identity - by default why are the database primary key defined "nvarchar" and not "UniqueIdentifier"?
- Prepared statement not executing in node js with mssql
- Sql Server: Convert months in calendar year to months in fiscal year
- Import & export database with all data in SQL Server Management Studio
- second argument of function OBJECT_ID
- Cannot create login in stored procedure with EXECUTE AS OWNER?
- Select statement in cursor
- Different values returned by SQL Server 2000 and SQL Server 2014 when returning results using for xml explicit
- T-sql; adding positive and negative values with SUM()
- left join tables, use only one row from left table
- How to select and join field with max date?
- SQL Server : multiple transactions
- pymssql executes stored procedure but returns no results
- Shredding XML column in SQL Server into new tables
- Sum JSON properties across multiple rows
- SQL UPPER only specific word
- Update process CUBE
- How To change the column order of An Existing Table in SQL Server 2008
- Why does EXEC retport an error of MUST DECLARE SCALAR VARIABLE
- Deleted objects in a database SQL Server 2012
- How to format a column in SQL Server?
- Visual Studio 2022 localdb ((localdb)\MSSQLLocalDB) (ASP.NET Core)
- SQl query optimzation
- Parse column value based on delimeters
- How to find out the IsSchemaBound property of a view of another database
- Combining SQL join with count
- Is it possible to upgrade a SQL Server 2000 database to 2005 without knowing it?
- Show data in a-b format in sql server 2008
- Does the SSRS Report Server have a maximum number of columns it can cope with?
- Column data types changing during Replication
- SQL Server data type compatibilty
- SQL query for generating a full list of Probability outcomes
- any limit of SQL Server connection count?
- SQL Right Join on Non Unique
- sql server 2012 allowing remote connection
- Function With Table Return T-SQL
- SQL Server 'CONTAINS' slower than 'LIKE' with multiple expressions
- SQL How to return All products sold in last 30 days Include non sellers
- Database Mirroring doesn't work (SQL Server 2008)
- Explicit join syntax
- Combining results of different count(*) sql queries
- How to pass schema as parameter to a stored procedure in sql server?
- Recursive Query and Count (SQL Server)
- SQL Server Indexed View Question
- linux php connect to Server 2008 running MS SQL Server Express
- Bitwise Addition and Subtraction in a single sql statement
- SQL Alias Column Name Needed In CASE WHEN Statement
- SQL row output like column
- Suppressing "X rows affected" in SQL Server using sqlcmd and Java
- numeric to minutes in sql
- SQL Transpose Data with Column Name
- Interpreting sys.dm_db_missing_index_group_stats, especially inequality_columns in SQL Server
- Code for either MS Access or SQL Server
- Unable to Call Table-Valued Function
- What would be a good datamodel to describe a datamodel?
- SQL group by date difference
- Masive Update on Column Descriptions of SQL Server from Excel
- Trouble connecting to Microsoft SQL Server with Windows Authentication
- How to return a table that removed SQL rows that match two columns
- Will index be used when using OR clause in where
- Recording gaps between two times
- Issues with sqlSave in R?
- Infinite While Loop T-SQL
- How to count records in multiple tables containing the same column?
- Actual cause of full transaction log in SQL Server
- T-SQL Filter multiple values on one column based on another table column
- Visual Studio 2017 (Community) and SQL Server 2017 incompatible?
- Possible to read in csv data to SQL Server / SSMS with only read access
- pandas.read_sql_query() throws TypeError: 'NoneType' object is not iterable
- Small ms Sql query to get the max of an id with some criteria
- SQL Server SET FMTONLY is deprecated. How do you get the structure of multiple resultsets?
- Select from (select union all select) error
- Is there a reason to check for null inside multiple using clausule in c#?
- check if a non-clustered index exists on a table column
- Removing millisecond from datetime throwing error
- What collation selected for the Spanish language in sql server 2012?
- Issue with Window Function in SQL Server 2008 R2
- Deleting 50 rows in 3 tables using IN () in SQL Server so slow
- Defending vs an Insert SQL Injection attack
- How does DB2 Express compare to Sql Server Express?
- Get Duplicate records from a table on combination of Multiple Columns and show 'Y' for Duplicate and 'N' for Single records
- Median instead avg() in SQL Server 2012
- Using SQL Server stored procedure to match column name to variable
- How to convert multiple values in the row with comma separator into multiple columns?
- Modify XML values identified through cross apply
- Syntax error in pivoting a SQL Server table using VARCHAR columns
- SSRS Parameter doesn't display even though the query works
- How to insert custom date into table in SQL Server?
- Add increments of 10 to a select query in SQL
- SQL Select Max Date with Multiple records
- How many is too many databases on SQL Server?
- Limitations in number of files and filegroups in SQL Server 2012
- T-SQL return every other character from string
- Automatically switch between a build and reporting DB i .NET 4.5 and SQL Server 2012
- Best way to compare the end of a string, use RIGHT, LIKE or other?
- T-sql, finding elements not matched from list of keys
- SQL Server Management Studio For VS2010
- SQL: Compound Queries
- Glassfish/Toplink and sqljdbc.jar retrying forever on broken db-connection
- How to join to the TOP 1 while performing an INSERT?
- SQL XML Nested Elements
- The difference between A none clustered primary-key and a covering index in terms of performance
- how to resolve this - group by changes the Order of items in SQL Server
- MSSQL Returns Different result in SSMS and Node JS script
- how to convert hascode to string value
- How to import a text file with multiple layouts into SQL with SSIS
- The type 'Microsoft.SqlServer.Types.SqlGeography' exists in both 'Microsoft.SqlServer.Types.dll' and 'Microsoft.SqlServer.Types.dll'
- How to run SQL in SQL server using Pycharm?
- Select statement setting DateTime precision
- Sorting Merge Join output