We discussed in previous posts, how important to create index on your tables or views. However, next question will come to you what is number of indexes that you can create or will the be a performance decrease on number of indexes you create?

In all the previous cases, we consider only SELECT statements. But you know that SELECT is not the only statement we execute in our database environments. Let us consider what will happen if we have multiple indexes when we run INSERT and UPDATE statements.

Let us build create a table to save employee’s data.

CREATE TABLE [dbo].[Employee]( 
	[ID] [int] IDENTITY(1,1) NOT NULL, 
	[FirstName] [varchar](25) NULL,
	[LastName] [varchar](25) NULL, 
	[Age] [tinyint] NULL, 
	[ADDRESS] [varchar](150) NULL, 
	[Location] [varchar](10) NULL, 
	[Sex] [char](1) NULL, 
	[STATUS] [char](1) NULL, 
	CONSTRAINT [PK_Employee] PRIMARY KEY CLUSTERED ( [ID] ASC ) 
 ) ON [PRIMARY] 

Another table with same attributes but table name is employee2.

CREATE TABLE [dbo].[Employee2]( 
	[ID] [int] IDENTITY(1,1) NOT NULL, 
	[FirstName] [varchar](25) NULL, 
	[LastName] [varchar](25) NULL, 
	[Age] [tinyint] NULL, 
	[ADDRESS] [varchar](150) NULL, 
	[Location] [varchar](10) NULL, 
	[Sex] [char](1) NULL, 
	[STATUS] [char](1) NULL, 
	CONSTRAINT [PK_Employee2] PRIMARY KEY CLUSTERED ( [ID] ASC ) 
) ON [PRIMARY] GO 

You can see that both tables has primary key clustered on ID column. I populated this two tables with 1000 records (Thanks to Red-Gate Data Generator) Now will create additional indexes on employee table like following.

CREATE INDEX ix_Ind1 ON dbo.Employee ( FirstName, LastName ) 
GO 
CREATE INDEX ix_Ind2 ON dbo.Employee ( LastName, FirstName ) 
GO 
CREATE INDEX ix_Ind3 ON dbo.Employee ( Address ) 
GO 
CREATE INDEX ix_Ind4 ON dbo.Employee ( Location ) 
GO 
CREATE INDEX ix_Ind5 ON dbo.Employee ( Sex ) 
GO 
CREATE INDEX ix_Ind6 ON dbo.Employee ( Status ) 
GO 
CREATE INDEX ix_Ind7 ON dbo.Employee ( Age ) 
GO 
CREATE INDEX ix_Ind8 ON dbo.Employee ( Sex, Status ) 
Go 
CREATE INDEX ix_Ind9 ON dbo.Employee ( Sex, Status, Age ) 
GO 

So now we have nine indexes on employee table covering nine scenarios.

No we will execute Update and Insert statements on both tables to compare the results.

UPDATE Employee 
SET FirstName = 'Dinesh' 
WHERE Id = 5 

UPDATE Employee2 
SET FirstName = 'Dinesh' 
WHERE Id = 5 

As we were doing all along, let us compare query costs.

Clearly a different. Table with many indexes cost is 71% while table with less indexes cost is 29%. Let is do this for a insert as well.

INSERT Employee ( 
	FirstName, 
	LastName, 
	Age,
	ADDRESS, 
	Sex, 
	STATUS ) 
 VALUES ( 
	 'Dinesh',
	 'Asanka', 
	 35,
	 'Kelaniya, Sri Lanka',
	 'M',
	 'Y' ) 
 
INSERT Employee2 ( 
	FirstName, 
	LastName, 
	Age,
	ADDRESS, 
	Sex, 
	STATUS ) 
 VALUES ( 
	'Dinesh',
	'Asanka', 
	35,
	'Kelaniya, Sri Lanka',
	'M',
	'Y' ) 

If you measure the query costs again from following image,

You can see that table with indexes has taken 91% while other table has cost only 9% query cost.

Why is this?

When ever you insert/update your data, database engine has to update it’s index structure and it’s data as well. This needs additional resources.

Apart from the performance there is another consideration as well.

sp_spaceUsed 'Employee' 
GO 
sp_spaceUsed 'Employee2' 
GO 

When execute above query, your result will be

You can see that table with more indexes is required more space than the other table.

So all in all, when you are creating indexes, make sure that it is absolutely necessary to create that index. Otherwise, creating unnecessary index doest not enhance your performance while it decrease your performances.