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.
/****** 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', 'firstname.lastname@example.org', 0, CAST(127.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2018, 'aakankshi6', 'email@example.com', 3, CAST(109.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2011, 'Aankashi', 'firstname.lastname@example.org', 1, CAST(15.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2006, 'Hr. Niels Henriksen', 'email@example.com', 1, CAST(30.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2007, 'Neeraj Singh', 'firstname.lastname@example.org', 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', 'email@example.com', 1, CAST(25.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2010, 'Test 10', 'firstname.lastname@example.org', 0, CAST(5955.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1003, 'Test 2', 'email@example.com', 2, CAST(30.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1004, 'Test 2', 'firstname.lastname@example.org', 0, CAST(15.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1005, 'Test 3', 'email@example.com', 1, CAST(31685.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (1006, 'Test 4', 'firstname.lastname@example.org', 0, CAST(25.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2004, 'Test 5', 'email@example.com', 0, CAST(30.00 AS Decimal(18, 2))) GO INSERT [dbo].[TblUsers] ([Id], [Name], [Email], [Gender], [RewardsPoint]) VALUES (2008, 'Test 8', 'firstname.lastname@example.org', 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 product many catgeorys
- Set Row security with Condition
- Repeating Record Sequence using SQL
- Is it possible to insert data to 2 tables from 1 controller?
- Unique rows per day
- DMF and DMV in SQL Server 2008
- How capture an error like "Package Validation Error"
- Is date/time part of SQL Server's datetimeoffset UTC or local?
- SQL Server Log info for DELETE row(s) on a table
- Speed up SQL query with nested query
- Regional problems with C# and SQL Database
- Calculating the sum of rows that have been grouped in SQL
- SqlBulkCopy inserts when inside a transaction prevents any other writes on a table
- Multipe DataSet Values in one Row
- sql server 2008 - t-sql does not recognize column name from the table
- Does the location of a stored procedure affect its performance?
- SQL query for finding difference of two time and sum
- SQL Server - Return All possible combinations of a 4 digit number passed to a stored procedure
- Formatting Database Schema From SQL Server
- mysql error inner join
- How to set up a trigger across databaes in SQL Server 2005?
- SQL Server: not like (<>) hides null values
- What is the T-SQl syntax to transform data from a single table into this specific format?
- Paging through Large table in sql 2005
- Cannot resolve the collation conflict error with Temp Table
- SQLAlchemy fails to insert decimal only on temp table
- Insert into sql_variant column in sql server
- SQL: partial group by
- How to export result of SQL server - "New Data Comparison" to an Excel file?
- Syntax for SQL Trigger to Insert Data in another DB and also to update any field in another db adfter a field is edited
- SQL Get columns data in a single column
- Can you query the maximum database size based on the SQL Server license?
- Top X by Day, by Person
- Find time of a MAX value in SQL Server
- Why does my paymentwall pingback not update the DB and I get an error on pingback test?
- What is the best way to transfer a table or tables from one SQL server to another?
- Generate a block of serial numbers from SQL Server while handling concurrent connections?
- How to split one column into two columns?
- Can't connect to SQL Server from Node
- T-SQL Cumulative Count Reset per Month
- Flask-SQLAlchemy (mssql+pyodbc) and Existing Models with Blueprint
- inserting date time with microsecond into SQL Server from python
- UTC time stamp to Datetime in Sql server
- SQL incrementing limit
- Is it possible to write single query for following scenario?
- MS Access Date() Function and SQL Server Query
- SQL working as query, but failing when calling as a stored procedure
- java.lang.UnsatisfiedLinkError: Native Library sqljdbc_auth.dll already loaded in another classloader
- Indexing using Database Engine tuning advisor
- SQL Server GROUP BY Hour over Midnight
- Connection string USING Application Intent errors
- How to copy a table to another with primary key?
- Set field DateTime to show the date and time in the format dd/mm/yyyy hh:mm:ss
- Select SUM if NULL - UPDATE "0"
- Why should I use the dbDacFx provider at all?
- Questions about indexing in SQL
- How to make the merge query to do insert once in SQL Server
- Find if any columns that contain null in SQL Server
- Include aggregate function results in SELECT
- How to block records when are read?
- SQL Sum function of a decimal column returns int instead of decimal
- dynamically add multiple textBoxes in the form using C#
- PHP PDO connection with MS SQL Server using ODBC
- SQL: extract common string from by group
- CLR trigger in a web-application?
- Problem with audio
- Can a view return multiple result sets?
- Inner Joining three tables
- pymongo typeError: document must be an instance of dict, bson.son.SON, bson.raw_bson.RawBSONDocument
- How to change delimiter in C# datatable
- SQL Server 2005 Reporting Services - Configuring the Unattended Account - Invalid domain\alias
- How to determine which version of SQL Server is running using T-SQL
- Ways to join and update tables in SQL
- Cannot resolve collation conflict for column 4 in SELECT statement
- How to roll back UPDATE statement?
- How to use coordinates from a sql table as parameter input in Weather URL API SSIS script?
- how to get sub account under of mother account
- Pivot rows into columns with fixed rows but unknown content in SQL Server
- Function name is ambigious in SQL server
- I want a case statetement that count more than 1 as 1
- TSQL Cursor automatically rounds up money type variables
- Delete field Duplicates from the Same table
- SQL Server conversion fidelity from nvarchar to varbinary, then from varbinary to nvarchar
- SQL - Column value manipulation -return dataset
- HeidiSql connection to MS SQL Server LocalDB
- divide payments for periods
- Kubernetes: Failed to join the instance 'mssql-secondary1' to the availability group 'fghyt'. (Microsoft.SqlServer.Management.HadrModel)
- How to pivot? How to convert multiple rows into one row with multiple columns?
- Azure SQL Server - Messages only in english
- Replace value by group with condition in SQL
- itgendid012: Last part of the SQL statement has not been recognized on distributed Exact Online query
- Oracle Linked Server in SQL Server 2017
- What is the "DATA" keyword in T-SQL for?
- SQL Server Group By - Aggregate NULL or empty values into all other values
- Replace single quotes in SQL Server
- Calculate running total SQL Server stored procedure
- ** Restricted Text ** when Reviewing Execution Plan in SQL Server Management Studio
- Why am I getting "There is already an object named ... in the database"?
- BCP - Invalid character value for cast specification when importing to table with IDENTITY column
- SQL : Update ID; According to Date
- SQL - Only accept capital letters by constraint
- SQL Query: Count, Group by date and year multiple columns
- Error in Updating a table using datetime as parameter in Stored procedure
- SSIS Data Flow OLE DB To Excel Nvarchar Size Issue
- Inserting/updating huge amount of rows into SQL Server with C#
- Access to SQL Server 2005 from a non-domain machine using Windows authentication
- SQL Server Query Getting Distinct Page_Name
- Is FORMATMESSAGE returns several string for one parameter value?
- inserting into a view in SQL server
- How to connect to sql server database via LAN
- SQL Server update multiple record from list of records
- Integration of a c# class library in SQL Server
- SQL Show Number of Months and Years Between Two Dates
- Select average of product rate using date backtracking
- How Can I speed up Multiple Left Outer Join,
- What database type should I define CUSIP as?
- MS SQL QUERY Vs SPARK SQL return difference total of rows
- I need to write a sql query to display top 25 records with a catch
- C# timer for database cyclic polling
- Replacement for UPDATE statement with ORDER BY clause
- Merge data using Integration Service
- SQL Server : Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '='
- Converting Access calculated field to T-SQL
- Group by with count
- Write A DataTable with variable columns into a database table
- 'Invalid attempt to read when no data is present' - exception happens "sometimes" in Entity Framework
- SQL ISSUE- Help in query count + min
- MAX on group returns multiple values with same date but different times
- How to Bulk insert Hindi Text in SQL Server
- Delete the column for the particular value
- How do you access the Context_Info() variable in SQL2005 Profiler?
- Insert negative value into SQL Server decimal column throws error
- How to know SSIS run package issue when trying to execute
- How Do I Ignore Errors When Deleting Records
- SET IDENTITY_INSERT ON List In Database SQL Server
- SQL "WITH" Performance and Temp Table (possible "Query Hint" to simplify)
- Using SQL Replace to emulate group-concat, but getting the wrong results
- Can this simple SQL query be optimized?
- SQL: creating a string array and query = like all strings in array
- Updating a column in SQL Server with difference in value of some column in another table
- Split string, copy data into multiple tables
- How to have conditional group by in sql?
- Help with a query
- Having trouble with a database query using SQL Server Management Studio
- Entity Framework LINQ for finding sub items from LastOrDefault parent
- SQL Server 2008 - "Conversion failed when converting from a character string to uniqueidentifier." error
- Create dummy data with WHILE
- SQL Server - Selecting multiple maximum values matching two separate corresponding cells
- How to use a While Loop in SQL to Create a Variable
- SQL query with ISNULL and computed column does not return result