| JXStern 2006-10-24, 6:30 pm |
| On Fri, 22 Sep 2006 10:51:32 +0200, "Tibor Karaszi"
<tibor_please.no. email_karaszi@hotmai
l.nomail.com> wrote:
>
>No, that was not what I was trying to say. It is not really that simple. If you want to get into
>this subject, you need to learn to read execution plans. Sometimes, a filter (WHERE) is performed
>before the JOIN operation, sometimes not. It depends on so many factors what execution plan you end
>up with. Start with simpler queries, read the query and try to understand what the element in the
>execution plan are doing and map them to the elements in your queries.
>
>I tried to describe the concept of a covering index in my earlier reply, but not having English as
>native language, the description might not be the best. I suggest you read in Books Online and also
>Google on "covering index" etc...
Tibor, your English is fine, but the situation is mildly surprising.
It sure surprised me that the advisor would say this! Let's start
with this from OP. It is not true that a clustered index is a "better
kind" of index than a non-clustered one. It is different, and what is
better depends on how it will be used.
One kind of internal operation the SQLServer compiler/optimizer may do
is a hash-match, in which it needs to compare the keys of two tables.
When these keys are available in non-clustered indexes, they are most
easily fetched. When one (or both) is available only as a clustered
index, the entire table must be scanned just to get index values. For
large tables with long records, that can be much more expensive!
The clustered index only occassionally (for certain schemas used with
certain data accessed in certain ways) helps in execution speed,
especially in these days of multi-gigabyte RAM systems that cache so
much. And SQLServer has an unhealthy dependence on clustered indexes
for best memory management and defragmentation.
So yes, OP, you asked a very interesting question. Hope you've gotten
something of an answer, and that lots of us have learned something.
Josh
|