Non Clustered index - With INCLUDE clause

Question

Non Clustered index - With INCLUDE clause

Old McDonald

SSCertifiable

Points: 5768
More actions
June 2, 2009 at 2:40 am

#205387

I am maintaining a data mart and came across a table in that is having 3 non-clustered indexes on the same columns. The difference between the 3 indexes is that one of the index uses 2 columns in the include clause and the second once uses 1 column in the include clause and the third one doesn't include any columns.
Following is the code:
IF object_id('MyTable') IS NOT NULL
DROP TABLE MyTable
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[MyTable](
[SK_ID] [bigint] IDENTITY(1,1) NOT NULL,
[ACCOUNT_NO] [varchar](20) NULL,
[CARD_NUMBER] [numeric](19, 0) NULL,
[TRANS_TYPE] [numeric](3, 0) NULL,
[TRANS_COUNT] [numeric](7, 0) NULL,
[TRANS_VALUE] [numeric](15, 2) NULL,
[CURRENT_DATE] [smalldatetime] NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
GO
CREATE NONCLUSTERED INDEX [NIX_WITHINCLUDE] ON [dbo].[MYTABLE]
(
[TRANS_TYPE] ASC,
[ACCOUNT_NO] ASC
)
INCLUDE ( [TRANS_COUNT],
[CURRENT_DATE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NIX_WITHINCLUDE_1] ON [dbo].[MYTABLE]
(
[TRANS_TYPE] ASC,
[ACCOUNT_NO] ASC
)
INCLUDE ( [CURRENT_DATE]) ON [PRIMARY]
GO
CREATE NONCLUSTERED INDEX [NIX_WITHOUTINCLUDE] ON [dbo].[MYTABLE]
(
[TRANS_TYPE] ASC,
[ACCOUNT_NO] ASC
) ON [PRIMARY]
I have read BOL and other articles about INCLUDE clause and my conclusion is that NIX_WITHINCLUDE is good enough to cover everything that will be covered by all 3 indexes.
Do I actually need NIX_WITHINCLUDE_1 and NIX_WITHOUTINCLUDE indexes?
Please clarify my doubt and let me know if I am wrong.
These indexes and slowing down my load process.
-Vikas Bindra

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 1

It is impossible to give you a recommendation about the indexes without knowing what queries you have and the selectivity of the queries. An index that is created with include columns is created in order to create a covering index for a specific query. If the query returns few records, then the included columns won't help much because it has to do small amount of lookup. If the query returns lots of records, then the included columns can be very beneficial. If I'll assume that in your case both indexes (with the include) are very beneficial, most chances are that I'd have only one index based on Trans_type and account_no columns that also have the include columns Trans_type and current_date.

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Old McDonald SSCertifiable Points: 5768 More actions · Answer 2

Thanks Adi for your reply.

I am very new to the system. Still need to dig out what all queries are getting fired on this table. I have got around 12 million records in this table. And daily load is slowing down with more and more records inserted into this table. I have archived old data from this table, still not much gain.

I agree with you that I can not drop/change any index before i find out the queries,

But in general, if all 3 are very beneficial, should i keep only the one that includes Trans_type and current_date?

-Vikas Bindra

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

vikas bindra (6/2/2009)
Do I actually need NIX_WITHINCLUDE_1 and NIX_WITHOUTINCLUDE indexes?

I can't see any reason why you would. It is possible that SQL would use the smaller ones occationally, but the one index is a super-set of the other two.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Old McDonald SSCertifiable Points: 5768 More actions · Answer 4

Thanks Gail for the feedback!

The current system is really messed up; apart from the above example there are lot many others.

They also have loops and cursors all over the load process:crying:

-Vikas Bindra

Adi Cohn-120898 SSC-Dedicated Points: 33944 More actions · Answer 5

vikas bindra (6/2/2009)
Thanks Adi for your reply.
I agree with you that I can not drop/change any index before i find out the queries,
But in general, if all 3 are very beneficial, should i keep only the one that includes Trans_type and current_date?

If I wouldn't have time to check the queries, then this is what I'd do. If I'd have time to check it and see if I really need to the included columns, I'll might use a different index I wouldn't have more then one index (from the indexes that you wrote about).

Adi

--------------------------------------------------------------
To know how to ask questions and increase the chances of getting asnwers:
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Old McDonald SSCertifiable Points: 5768 More actions · Answer 6

Thanks Adi!

Time is the problem...its always is:-)

-Vikas Bindra