CODEWITHSUNDEEP
sqlsql-servert-sqlindexingsql-server-2008-r2
Fletch
Fletch

Reputation: 367

Include columns in an existing non-clustered index or add a new non-clustered index

Table Props already has a non-clustered index for column 'CreatedOn' but this index doesn't include certain other columns that are required in order to significantly improve the query performance of a frequently run query.

To fix this is it best to;

    1. create an additional non-clustered index with the included columns or
    2. alter the existing index to add the other columns as included columns?

In addition:

A simplified version of the table is below along with the index in question:

CREATE TABLE dbo.Props(
    PropID int NOT NULL,
    Reference nchar(10) NULL,
    CustomerID int NULL,
    SecondCustomerID int NULL,
    PropStatus smallint NOT NULL,
    StatusLastChanged datetime NULL,
    PropType smallint NULL,
    CreatedOn datetime NULL,
    CreatedBy int NULL
CONSTRAINT PK_Props PRIMARY KEY CLUSTERED 
(
    PropID ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
) ON [PRIMARY]
GO

Current index:

CREATE NONCLUSTERED INDEX idx_CreatedOn ON dbo.Props
(
    CreatedOn ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 90) ON [PRIMARY]
GO

All 5 of the columns required in the new or altered index are; foreign key columns, a mixture of smallint and int, nullable and non-nullable.

In the example the columns to include are: CustomerID, SecondCustomerID, PropStatus, PropType and CreatedBy.

Upvotes: 0

Views: 900

Answers (1)

Jason A. Long
Jason A. Long

Reputation: 4442

As always... It depends...

As a general rule having redundant indexes is not desirable. So, in the absence of other information, you'd be better off adding the included columns, making it a covering index.

That said, the original index was likely built for another "high frequency" query... So now you have to determine weather or not the the increased index page count is going adversely affect the existing queries that use the index in it's current state.

You'd also want to look at the expense of doing a key lookup in relation to the rest of the query. If the key lookup in only a minor part of the total cost, it's unlikely that the performance gains will offset the expense of maintaining the larger index.

Upvotes: 2

Related Questions