0

When creating indexes and especially those with multiple columns, should I look to combine as many columns in the index as would be useful?

I've ran a script on the stats to pick up missing indexes and there's some suggestions such as:

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [StringId], [DateNextDeadline])

And

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [DateNextDeadline])

And

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [ClientId], [DateNextDeadline])

They all have the same [Id] and [DateNextDeadline] columns in there, and they all are indexed on the same two main columns.

Should I, in this instance, create a single index with ([Id], [StringId], [ClientId], [DateNextDeadline]) and will SQL Server use the same column indexes to and pick the including columns depending on the query? Or would it only use the index if there's an exact match? So I would need to create three indexes?

1

Based on those 3 specific recommendations, I would use

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE ([Id], [ClientId], [DateNextDeadline],[StringID])

The equality/inequality columns are the same, which is the bigger part. SQL Server should use this for any queries that have all or a subset of those Included cols.

And of course, you run this through a rigorous set of test queries to verify :)

4
  • 1
    "And of course, you run this through a rigorous set of test queries to verify :)" Oh yes, of course said everyone to their management before hitting F5! – RemarkLima Feb 26 '18 at 15:42
  • 3
    Assuming ID is in the Clustered Index, you can leave that out of the INCLUDE fields, as SQL Server includes it anyway brentozar.com/archive/2015/08/…. Not really any benefit either way, except potentially less typing...! – Mark Sinkinson Feb 26 '18 at 15:47
  • @RemarkLima , Of course if id is CI then it should be remove from include list.With existing index what if I have to query on where [OpenClosedStatus=? and there is no ContactID in where.Will index be utilize ? – KumarHarsh Feb 27 '18 at 11:16
  • @KumarHarsh I guess that's what I'm asking - if there's a query which only checks the OpenClosedStatus will the index be missed? Also, if it only need the DateNextDeadline will it still hit the index? – RemarkLima Feb 27 '18 at 12:30
1

Should I, in this instance, create a single index with ([Id], [StringId], [ClientId], [DateNextDeadline]) and will SQL Server use the same column indexes to and pick the including columns depending on the query? Or would it only use the index if there's an exact match? So I would need to create three indexes?

If there is index seek in query plan then covering index is utilize else no.

I am trying to answer only "Multiple column or single column index and ordering of index in composite index"

Index will be utilize or not depend on Query Optimizer (QO) or Selectivity of that column.

Suppose in query if more number of rows are return then QO may index Scan if it find that Index Scan is more cost effective than Index Seek.

So index column should be more selective in clustered or nonClustered index so that QO choose Index Seek.

So in composite index most selective index should be towards left . In other word "it should be in most selective order".

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus] ON [tbl]
([ContractId], [OpenClosedStatus]) INCLUDE (ID,[StringId],[ClientId], [DateNextDeadline])

If ID is Clustered index then remove from the covering list.

I have a query which filter on both column

where [ContractId]=? and [OpenClosedStatus]=?

if ContractId is more selective than OpenClosedStatus then order of column in composite index is ok .

Now QO will decide if it is selective enough to Index Seek or go for Index Scan.

It depend upon other factor too.

If I have another query where i will use

only where [OpenClosedStatus]=?

Here if OpenClosedStatus is selective enough then may be creating separate index on [OpenClosedStatus] is good choice.if OpenClosedStatus is not selective enough then there is no point in creating seperate index this column.

Suppose OpenClosedStatus is bit type then it may return lot of rows so it is not selective enough.

But OpenClosedStatus may work ok in IX_Tbl_ContractId_OpenClosedStatus composite index.

if I have query only on where DateNextDeadline

So in our example it will never hit index because there is no index on this column,it is included in cover index.cover index purpose is some thing else.

I have query which use all three column then my index will be like,

CREATE INDEX [IX_Tbl_ContractId_OpenClosedStatus_1] ON [tbl]
([ContractId],[DateNextDeadline], [OpenClosedStatus]) INCLUDE (ID,[StringId],[ClientId] )

where [ContractId]=? and [DateNextDeadline]=? [OpenClosedStatus]=?

Suppose all columns are selective enough, and if you have different query where any or all column will be use to filter then you may create seperate index.

So First understand your workload,

i) Which all column be use in 'join , where,order by, group by' condition.

ii) Which query will be very frequently use and how important it is.

iii) if Table has static and less number of rows then point in creating index on that table.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.