Does Index Improve SQL Server Query Performance?

Yes, In SQL server, Indexing a table really helps query performance to a great extend. especially if the table has a lot of records. let’s see an example. I’m creating two tables Table_1 without any index except the primary key. Table_2 with an index in a column other than the primary key.

Script For Creating Table_1:

CREATE TABLE [dbo].[Table_1](
[Sl_no] [int] IDENTITY(1,1) NOT NULL,
[Category] [int] NULL,
[Name] [varchar](30) NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[Sl_no] ASC
))
GO

Script For Creating Table_2:

CREATE TABLE [dbo].[Table_2](
[Sl_no] [int] IDENTITY(1,1) NOT NULL,
[Category] [int] NULL,
[Name] [varchar](30) NULL,
CONSTRAINT [PK_Table_2] PRIMARY KEY CLUSTERED
(
[Sl_no] ASC
))
GO

Create Index for the category column in Table_2:

CREATE NONCLUSTERED INDEX [IX_Table_2]
ON [dbo].[Table_2] ([Category])
INCLUDE ([Sl_no],[Name])
GO

Insert million records to Table_1 and Table_2:

Declare @i as integer
Set @i = 1
begin transaction
while(@i <= 1000000)
begin
   Insert into Table_1 values (cast(rand() * 99 as numeric(2,0)) , 
                           'Name ' + cast(@i as varchar(10)))
   Insert into Table_2 values (cast(rand() * 99 as numeric(2,0)) , 
                            'Name ' + cast(@i as varchar(10)))
   Set @i = @i+1
End
commit transaction

Check the data in both the tables and make sure they are the same

SQL Server Index Performance 1

Now select the Include Actual Execution plan and then run select queries against the two tables at the same time. The select queries should have a where clause condition for category column. Check the timing of query execution. See the below results I got. The select query against the table without index in category column took 99% of the total query cost consumed by both the queries. This clearly shows the importance of Index in the table.

SQL Server Index Performance 2

 

Do you what to test whether WITH (NOLOCK) improves performance of sql queries. Read the article SQL Server: Does with (nolock) increase performance?

 

Leave your thoughts...