Welcome to Appsloveworld Technologies, In this session, we learn about what indexes are, why do we use indexes, and the advantages of using them.
What is indexes?
Indexes are used by queries to find data from tables quickly. Indexes are usually created on tables and views. Index on a table or a view is very much similar to an index that we find in a book.
If you don’t have an index in a book and I ask you to look at a specific chapter in that book, you will have to look at every page starting from the first page till the end.
On the other hand, let’s assume there is an index in the book. In that case, you look up to page number of the chapter with the index and then directly go to that page number to look at that chapter.
So the book index is helping to reduce the time it takes to find the chapter.
In a similar fashion table and view index can help the query to find data quickly. In fact, the existence of the right indexes can drastically improve the performance of the query.
If there is no index to help the query, then the query engine just like we do. it has to scan every row in the table from the beginning till the end. And this is called a table scan in database terminology and table scans are bad for performance.
When does a query result in a table scan?
whenever there is no index to help that query, in that case, the database engine has to scan every row from the beginning till the end to find the data.
So if the table is large enough, then it’s going to affect the performance of the query very badly, just like how it takes a longer time for us to find, the chapter number, or some keywords if there is no index based on that chapter or keywords.
Let’s look at an example.
We’ve got TblUsers which has Id, named, Email, gender, MyRewards, and MyCash columns.And this TblUsers table does not have an index on the MyRewards column.You can find TblUsers Sql Table Script at end of the post.
if you look at the MyRewards, so these values are not arranged in ascending or descending order in the table.
Obviously, if I have to find,Users who’s MyRewards between 50 and 100, I will have to scan each and every row as there is no index on this table to help this query.
So obviously, the database engine has to scan every row from the beginning till the end. It has to scan the entire table. And this is called a table scan and table scans are very bad.
How to create an Index?
So let’s see how to solve this problem with the help of an index. So to create an index on a table, we use the “create index” statement
Just ike to create a table with “create table” table name ,to create a stored procedure, we’ll use “create proc” procedure name ,similarly to create an index we use, create index, and the index name.
Since we use “sp” for stored procedures, “tbl” for tables, for indexes usually we use IX, underscore the name of the table on which you are creating the index.
CREATE Index IX_TblUsers_MyRewards
ON TblUsers (RewardsPoint ASC)
In this case, we are creating an index on TblUsers. So “_” TblUsers on the column within the table on which we are creating this index.
In this case, we are creating an index on the MyRewards column. So underscore MyRewards on the table on which we are creating the index TblUsers and on MyRewards column within that table.
How do you want the entries in the index to be arranged?
we are creating an index on the MyRewards column.So in the index, the MyRewards column is the key.
So how do you want these key values to be arranged?
Do you want them to be arranged in an ascending or descending order? You can control that using ASC for ascending and DESC for descending keywords.
so we are creating an index on MyRewards column in the ascending order.
And if you look at the index, all the MyRewards values are arranged in ascending order.
So once we have this index and if we execute that query, now we know that all RewardsPoint between 50 and 100 are somewhere at the bottom of the index because they are stored in ascending order.
if you look at every entry in the index, for example, the first row with RewardsPoint 11 has got a real address, just like how we have page numbers in the book.
The rows have addresses, the actual index may look slightly different .This is for our understanding ,I have entered a row address here but in fact, it will be some hexadecimal number.
So each row has a row address So it will directly go to the table and then pick up that record, just like how if we have a page number, we go to that page number directly.
So instead of scanning each individual row in the table, I am now able to seek the index and then pick up the addresses from the index, go to the table, and using those addresses, retrieve those records directly rather than scanning each room in the table.
obviously, this is going to, improve the performance of this query.
How see created index?
Expand the tables folder and we have TblUsers. Now if we expand the TblUsers table, you should see an index folder and if you expand the indexes folder you should see an index that we have just created.
Table Script
/****** Object: Table [dbo].[TblUsers] Script Date: 09/28/2020 4:25:59 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[TblUsers](
[Id] [int] NOT NULL,
[Name] [nvarchar](100) NULL,
[Email] [nvarchar](max) NOT NULL,
[Gender] [int] NOT NULL,
[RewardsPoint] [decimal](18, 2) NOT NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2012, 'Aakankshi Gupta', 'aakankshi@gmail.com', 0, CAST(127.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2018, 'aakankshi6', 'aakankshi6@gmail.com', 3, CAST(109.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2011, 'Aankashi', 'alokpatel@gmail.com', 1, CAST(15.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2006, 'Hr. Niels Henriksen', 'contact@adequateinfosoft.com', 1, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2007, 'Neeraj Singh', 'neirajsingh101@gmail.com', 0, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (3, 'Pankaj Patel', 'Pankaj@gmail.com', 0, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2, 'rajat Saxena', 'rajat.nirmal@gmail.com', 1, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2010, 'Test 10', 'test02@gmail.com', 0, CAST(5955.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1003, 'Test 2', 'pankajabc@gmail.com', 2, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1004, 'Test 2', 'pankajabcd@gmail.com', 0, CAST(15.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1005, 'Test 3', 'test@test.com', 1, CAST(31685.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1006, 'Test 4', 'test1@test.com', 0, CAST(25.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2004, 'Test 5', 'pankajws@gmail.com', 0, CAST(30.00 AS Decimal(18, 2)))
GO
INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2008, 'Test 8', 'test3@test.com', 0, CAST(109.00 AS Decimal(18, 2)))
GO
The post How does database indexing work with real time example 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
- SQL Comparing two Stored procedures between databases
- Delete statement in SQL is very slow
- How to get the data between mth and nth occurrence in a string
- What should the itemcount property in SQL Server match
- SQL Server: Splitting string by '/' and put each split element into a different column
- Ridding this query of Hash Match Join?
- How to retrieve multiple records of a particular id in one row using SQL Server
- how to save updated data into sql server from model derivative api of autodesk forge
- How to Remove a Ghost Server in Web Farm After Migrating a Reporting Services Installation?
- SQL Where clause order
- Delete multiple rows by sql statement "delete from tableName" whereas TRIGGER "After Delete" had applied on that
- Elegant way to delete rows which are not referenced by other table
- Unable to drop Primary Key from SQL Server table
- Querying Random 10 Percent for Each Record in SQL Server 2008
- Select from table group by id where all records in that group have a flag = false
- INSERT a row only with default or null values
- Postgresql function from a SQL Server stored procedure
- Can we split a string using STRING_SPLIT() in WHERE clause to compare the same with more than one value?
- How to insert other values into database in Visual Studio when there is an ID auto incremented
- Checking for bracketed varchar in SQL Server
- How to setup NLog to show sqlexception linenumber in log?
- Can I move SQL Server data along one column to the left if previous column cell is NULL?
- how to pass variables like arrays / datatable to SQL server?
- How can I import existing local tatuk shape to ms sql server
- Does sql server minds the way records where inserted?
- Can't edit in sql database in Visual studio 2013
- Slow down execution of bulk inserts
- When a table in SQL Server has a large-ish nvarchar (say nvarchar(2000)) when the row is stored in the page, do all 4000 bytes get stored?
- C# LINQ: How to stack LINQ queries correctly
- Single conversation in Service Broker
- How to add biweekly periods in a year to a table? (SQL Server)
- Can I set variables in an SSIS for loop based on a query?
- How to find Apriori association in T-SQL statement?
- Using a SQL Ranking Function with a derived column
- Printing same rows twice in SQL Server
- Entity Framework 6 DBContext with only a subset of all tables
- SQL Server Compressing Adjacent Date Ranges
- Table variable in SQL Server
- SQL Server - Order by case
- update table by value in other table which holds multiple records
- Get count of rows returned by a locked stored procedure WITHOUT @@rowcount
- Dapper with SQL Server output bigint parameter returns int32
- SQL Server - Aggregating a SUM for each row in a table with multiple conditions
- Select numbers with more than 4 decimal places
- How To Identify Continuous Spans Of Time And Compute Summary Statistics In Time-Series Data?
- SQL serial number by group
- T-SQL Replace XML node
- Should I use SQL cursors here?
- I'm stuck at creating a view by excluding the rows with fields having NULL values
- Extracting data between two delimiters in SQL Server?
- Does SQL Server optimize LIKE ('%%') query?
- Entity Framework 7 (Beta7) migration script generating invalid table name
- Open multiple instances of a HTA from the same file
- SSIS: can not run an sql task to update table
- Can a PERSISTED column reference an existing persisted field?
- Uploading CSV to SQL Server
- How to collect data to single row in sql server
- Primary key and unique key seperate exception handling | asp .net c#
- Order by quarter of hour in the same column
- Entity Framework There is already an open DataReader associated with this Command which must be closed first
- SQL View. Select count... Where
- How to Generate xml from sql for below pattern
- Rewriting subquery as JOIN?
- How to create a link server: target is SQL server 2000 on a SQL server 2008
- T-SQL to find if a Database is Subscribed on the Subscriber in Transactional Replication
- T-SQL Stored Procedure passing input, using output
- SQL Server 2016 Stored Procedure Syntax Error
- SQL Server date index lagging by one day
- SQL Server : delete where string value contains another value
- Login failed for user 'NT AUTHORITY\LOCAL SERVICE' in window service
- SQL Server equivalent to Oracle LEAST?
- ISNULL is not helping the data to show with empty strings in this query
- Select an ID if count is equal to 1
- Get ALL parents and count of children based on a where clause and left join
- How do I union multiple WITH clause in SQL Server 2008?
- How can I find information about updating or inserting a sql table row in sql server 2008?
- How to find fifth highest salary in a single query in SQL Server
- How to delete or truncate table in SQL Server?
- What's the difference with using "And" and "Between" in the where clause?
- sql insert rows from another table based on condition
- adventureworks 2005 and 2008
- How to capture additional SQL table values with an asp:DropDownList?
- Conditional SQL where clause
- Converting Varchar Date into Date Datatype
- Get Scope identity for multiple inserts
- Sql Server query executes insert twice instead of once
- How to create a Database user in Entity Framework
- c# return value from database using other field
- How to find if part of string appears more than one time SQL SERVER
- Using CASE and SELECT from another table using CONVERT
- How To Shorten This SQL Query Without A Million AND/OR's?
- Create table from a table in SQL Server
- Groupby and count() with alias and 'normal' dataframe: python pandas versus mssql
- SQL Server Pivot/Map column values to rows
- Any suggestions for identifying what indexes need to be created?
- SQL Server: Merge statement compilation error
- Does SQL Server cache scalar function results when used simultaneously in SELECT, WHERE and GROUP BY?
- SQL server - How to split one column into more columns?
- Splitting a column using character delimiter
- Getting List of Columns from a SQL Select statement
- Angular alphabetizes GET response
- Connect to remote SQL server 2012 using visual studio server explorer
- SQL: Take data from 3 records and combine to one
- SQL Parameters in C# aren't working as expected
- Sql query - how to get when a row first got a certain value
- How to Use FIRE_TRIGGERS in insert sql statement
- SQL server - privileges to get metadata of database
- How to make a SUM without group by
- How can I optimize this query used by a cursor?
- SQL Server: Determining games made more than 2 years ago
- inner join and group by
- Add data type to JSON values query
- Removing duplicates and combining data from one table in a SQL query
- Does the stream_id change if I move the file to some other directory within the same filetable?
- R version Machine Learning Server SQL Server 2019
- How to give row number for consecutive combination and duplicate it for duplicates
- Code has no errors, but it's not restoring the database
- Remove duplicates records from sum statement using SQL server
- Error converting varchar to int in SQL Server stored procedure
- Join and set two tables
- SQL Server encrypting data
- insert into looping based on select quantity
- Does Oracle have something like Change Data Capture in SQL Server 2008?
- Unable to preview SSIS Excel source data after skipping first few rows
- How to create a 'sanitized' copy of our SQL Server database?
- REPLACE within a CASE statement
- How to delete oldest week(period) in MS SQL database?
- Optimize lots of queries
- SQL Server 2012: Copy old data from "Production" to "Archive"-db each month
- Add object and its relationships atomically in SQL Server database
- MS SQL Server insert speed using SqlCommand
- Why is the performance of table value function better than select direct statement?
- Conditionally select value from one of two tables
- Remove the 'NUL' character in SSIS being imported from a text file
- MS SQL | How to query a filtered column (WHERE) with non filtered data
- Connection refused: connect Error in Java + Hibernate
- SQL Comma Split function for nth loop iteration
- SQL - Update rows between two values in a column
- In MDX how do I do a group by count(*) for a given dimension?
- Creating relationships between tables
- SQL Server permissions per column invisible to users
- Are there performance considerations to having 1000's of SQL Users/Logins on one SQL Server?
- How do you find the last time a database was accessed?
- Best way to count in joined table
- Determne if SQL Server database restored
- Concat variable #s of strings
- How does a JDBC driver implement the setMaxRows method
- Passing parameters into a Table-Valued Function
- Uniqueidentifier vs. IDENTITY vs. Material Code --which is the best choice for primary key?
- How do I import data into a SQL Server with auto detect column data type?