Introduction:
We are dealing with database and each and
every day increase data and we have face to deal problem related retrieve and
access of data from database and it will be taken too much time. So we need to increase
in data access speed.
Index (in simple words it like index of any book eg. While searching a word in Book we use index back of book to find the occurrence of that word
and its relevant page numbers) which
provides faster access to rows in tables. If we don’t create indexes then SQL
engine searches every row in table. We data grow in table then searching
without indexing become much slower.
In this article I want to show how to indexing on table
improve performance.
For that I will
create demo script with three tables.
First table I have
created “No Index” on Table and Insert
1, 00,000 records in table.
Second table “Clustered
Index” on table Column [ID] and Insert 1, 00,000 records in table
Third table having “Non
Clustered index” on table Column [ID] and Insert 1, 00,000 records in table
I am having following script with three tables and insert 1,
00,000 records into tables.
No Index on Table
-- Create Table
NoIndex Table
CREATE TABLE NoIndex
(
ID INT,
FirstName VARCHAR(100)
)
GO
GO
-- Insert One
Hundred thousan of Records
-- Insert 1
INSERT INTO NoIndex (ID,FirstName)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) %2 =1 THEN 'Mistry'
ELSE 'Nilay' END,FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
Cluster Index on Table
GO
-- Create Table
Index Table
CREATE TABLE ClusteredIndex
(
ID int,
FirstName VARCHAR(100),
)
GO
GO
-- Create Non
clustered Index on Last Name
CREATE Clustered INDEX
[IX_ClusteredIndex_ID] ON dbo.ClusteredIndex
(
[ID] ASC
) ON [PRIMARY]
GO
GO
-- Insert One
Hundred thousan of Records
-- Insert 1
INSERT INTO ClusteredIndex (ID,FirstName)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) %2 =1 THEN 'Mistry'
ELSE 'Nilay' END,FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
Non Cluster Index on Table
GO
-- Create Table
Index Table
CREATE TABLE NonClusteredIndex (ID int,
FirstName VARCHAR(100),
)
GO
GO
-- Create Non
clustered Index on Last Name
CREATE Clustered INDEX
[IX_NonClusteredIndex_ID] ON dbo.NonClusteredIndex
(
[ID] ASC
) ON [PRIMARY]
GO
GO
-- Insert One
Hundred thousan of Records
-- Insert 1
INSERT INTO NonClusteredIndex (ID,FirstName)
SELECT TOP 100000 ROW_NUMBER() OVER (ORDER BY a.name) RowID,
CASE WHEN ROW_NUMBER() OVER (ORDER BY a.name) %2 =1 THEN 'Mistry'
ELSE 'Nilay' END,FROM sys.all_objects a
CROSS JOIN sys.all_objects b
GO
Now, I am running with simple query as per following screen
and see execution plan for NoIndex, ClusterIndex and NonClusteredIndex tables.
Query 1 return table Scan operation and Query 2 and Query 3
return Index Seek Operation.
Index Seek which means that optimizer has found a useful index to
retrieve recordset. But if it is not able to do so either because there is no
index or no useful indexes on the table then SQL Server has to scan all the
records that satisfy query condition perform with Table Scan operation.
You clearly see above output which one is faster. Query 3
return same amount to percentage as ” No Index”
table is used that mean that Query3 is not used Non clustered index so
it will be bad decision to take Non Clustered Index on table [ID] column.
If suppose I forcefully assign Non Clustered Index on Query
then you will get following output as shown in following screen shot.
Conclusion:
For above example I explained you how index will be used to
increase performance with examples and also bad index decrease performance. So
take care when we used index on table. If index is not going too used further
delete it.
