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.
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]=?
ContractId is more selective than
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
OpenClosedStatus is selective enough then may be creating separate index
[OpenClosedStatus] is good choice.if
OpenClosedStatus is not selective enough then there is no point in creating seperate index this column.
bit type then it may return lot of rows so it is not selective enough.
OpenClosedStatus may work ok in
IX_Tbl_ContractId_OpenClosedStatus composite index.
if I have query only on
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.