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
- 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?