Indexing in SQL Server 2008

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.