Hi,
on one table I have this composite clustered index, setup in that
order:
IDF_Item, IDF_reference, Date, Container.

Is that order important like in a non-clustered index? or since it's a
unique value it doesn't matter?
because if the order is important the optimal order will be Date,
IDF_Item, IDF_Reference, Container


Thanks

Francois,

Yes, order is important if the index is to support partial matches to
queries. For example:

A. Query specifies: IDF_Item, Date

Your first index can seek for IDF_Item, but then will have to scan for the
Date.
The second index can seek for Date and IDF_Item.

B. Query specifies: Date

The index will likely not be used.

RLF


"bie2" <Francois.Tardif@gmail.com> wrote in message
news:1e9d2c15-1660-4a4b-aa12-96c9d8ef6622@i76g2000hsf.googlegroups.com...
> Hi,
> on one table I have this composite clustered index, setup in that
> order:
> IDF_Item, IDF_reference, Date, Container.
>
> Is that order important like in a non-clustered index? or since it's a
> unique value it doesn't matter?
> because if the order is important the optimal order will be Date,
> IDF_Item, IDF_Reference, Container
>
>
> Thanks

Hi Russell

Read Paul's article
http://www.sqlskills.com/blogs/paul/...edIndexes.aspx



"Russell Fields" <russellfields@nomail.com> wrote in message
news:%23b96EPptIHA.4260@TK2MSFTNGP05.phx.gbl...
> Francois,
>
> Yes, order is important if the index is to support partial matches to
> queries. For example:
>
> A. Query specifies: IDF_Item, Date
>
> Your first index can seek for IDF_Item, but then will have to scan for the
> Date.
> The second index can seek for Date and IDF_Item.
>
> B. Query specifies: Date
>
> The index will likely not be used.
>
> RLF
>
>
> "bie2" <Francois.Tardif@gmail.com> wrote in message
> news:1e9d2c15-1660-4a4b-aa12-96c9d8ef6622@i76g2000hsf.googlegroups.com...
>> Hi,
>> on one table I have this composite clustered index, setup in that
>> order:
>> IDF_Item, IDF_reference, Date, Container.
>>
>> Is that order important like in a non-clustered index? or since it's a
>> unique value it doesn't matter?
>> because if the order is important the optimal order will be Date,
>> IDF_Item, IDF_Reference, Container
>>
>>
>> Thanks
>
>

I would say that it depends on the kind of queries you execute against that
table. It is not important regards the number of duplicated rows by an
specific column on the key, opposite to a nonclustered index, but it is
important regards:

- insert rows at the latest pages, if for example column [Date] is
incrementing and you do not insert rows referencing past dates.
- using any range comparison, like "where [Date] between @sd and @ed",
because if column [Date] is not the first in the key, then those dates could
be distant one from the other.


AMB

"bie2" wrote:

> Hi,
> on one table I have this composite clustered index, setup in that
> order:
> IDF_Item, IDF_reference, Date, Container.
>
> Is that order important like in a non-clustered index? or since it's a
> unique value it doesn't matter?
> because if the order is important the optimal order will be Date,
> IDF_Item, IDF_Reference, Container
>
>
> Thanks
>

Uri (and Francois),

Yes, certainly, it is hard to choose and I am familiar with the issues Paul
writes about. (Also I made a mistake in typing my first response, mentioned
below.) But his blog certainly offeres a lot more detail than I did. Thanks
for including it.

In my first example to Francois, I illustrated a partial match with
unspecified columns between, instead of Paul's first example of an exact
match. The index can be used to seek to the IDF_Item, but it still has to
scan along through the qualifying entries to find those with the Date that
matches. If Francois rearranges the column order to have equality on the
first two columns the index can seek directly to the 'first' of the
qualifying entries.

In the second example, I meant to type "The _first_ index will likely not be
used". (Since I left out the word "first" it certainly was misleading.
Sorry about that, Francois.) However, the "likely not" is because of the
fact that, even then, the optimizer may still find it better to scan the
index for a trailing column, as Paul mentions.

Of course, this is looking at only one type of use of the two candidate
indexes. Other indexes may exist. Other queries may have different needs.
Et cetera. All of which, of course, can change the value of any particular
index.

RLF

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:u8dO6RptIHA.4876@TK2MSFTNGP02.phx.gbl...
> Hi Russell
>
> Read Paul's article
> http://www.sqlskills.com/blogs/paul/...edIndexes.aspx
>
>
>
> "Russell Fields" <russellfields@nomail.com> wrote in message
> news:%23b96EPptIHA.4260@TK2MSFTNGP05.phx.gbl...
>> Francois,
>>
>> Yes, order is important if the index is to support partial matches to
>> queries. For example:
>>
>> A. Query specifies: IDF_Item, Date
>>
>> Your first index can seek for IDF_Item, but then will have to scan for
>> the Date.
>> The second index can seek for Date and IDF_Item.
>>
>> B. Query specifies: Date
>>
>> The index will likely not be used.
>>
>> RLF
>>
>>
>> "bie2" <Francois.Tardif@gmail.com> wrote in message
>> news:1e9d2c15-1660-4a4b-aa12-96c9d8ef6622@i76g2000hsf.googlegroups.com...
>>> Hi,
>>> on one table I have this composite clustered index, setup in that
>>> order:
>>> IDF_Item, IDF_reference, Date, Container.
>>>
>>> Is that order important like in a non-clustered index? or since it's a
>>> unique value it doesn't matter?
>>> because if the order is important the optimal order will be Date,
>>> IDF_Item, IDF_Reference, Container
>>>
>>>
>>> Thanks
>>
>>
>
>

Yes it matters.

Just as with nonclustered indexes, if you do not have exact predicates
for all the columns in the index definition, then the order matters. See
Alejandro's examples.

The clustered index determines where newly inserted rows go (and where
changed rows are moved to). If the leading index column is a GUID then
your table will fragment like hell. Also explained already.

One thing you do not have to worry about when compared to nonclustered
indexes is the fact that the statistics of an index only have a
distribution histogram for the leading index column. In general, for a
nonclustered index it is a bad idea to have a column with low selectivy
as the first column.

If you have a nonclustered index and chose a leading column with low
selectivity, and the second column has a logarithmic distribution, then
the optimizer cannot make good decisions, because the distribution
histogram is no good, and the "all density" number is misleading. It
would make better decisions if the column with the logarithmic
distribution was leading and the evenly distributed column with the low
selectivity was second. You do not have this problem with the clustered
index, because in that case there is never the burden of expensive
bookmark lookups, and the choice will always be to use the clustered
index (or another covering index).

--
Gert-Jan
SQL Server MVP


bie2 wrote:
>
> Hi,
> on one table I have this composite clustered index, setup in that
> order:
> IDF_Item, IDF_reference, Date, Container.
>
> Is that order important like in a non-clustered index? or since it's a
> unique value it doesn't matter?
> because if the order is important the optimal order will be Date,
> IDF_Item, IDF_Reference, Container
>
> Thanks

Thanks all for your answer. This is explaining some issue I have. I
was just not sure about the clustered index. The info is really sparse
on this.