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
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.
Related Articles
- Do you what to test whether WITH (NOLOCK) improves performance of sql queries. Read the article SQL Server: Does with (nolock) increase performance?
- Considering performance, which is better UNION or UNION ALL?