Clustered Vrs Non Clustered Indexes

Dear All
I recently posted a message where I stated that a
clustered index is faster than a non clustered index, is
this correct or do I have my facts totally wrong ?
Thanks
Peter
Some definitions first:
An index doesn't have any speed to talk about; it isn't fast or slow. The optimizer generates
execution plans where those plans might use indexes in different ways. And when the execution engine
uses the plans, using an index or different index types influences performance.
A blanket statement like "clustered index are fasten than non-clustered indexes" is too much of a
generalization. There are cases where using a non-clustered index will result in fewer I/O
operations and shorter execution time compared to using a clustered index. One such example is where
a non-clustered index covers the query.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
> Dear All
> I recently posted a message where I stated that a
> clustered index is faster than a non clustered index, is
> this correct or do I have my facts totally wrong ?
> Thanks
> Peter
|||Thanks Tibor,
Ah well, wrong again ;)
Peter

>--Original Message--
>Some definitions first:
>An index doesn't have any speed to talk about; it isn't
fast or slow. The optimizer generates
>execution plans where those plans might use indexes in
different ways. And when the execution engine
>uses the plans, using an index or different index types
influences performance.
>
>A blanket statement like "clustered index are fasten than
non-clustered indexes" is too much of a
>generalization. There are cases where using a non-
clustered index will result in fewer I/O
>operations and shorter execution time compared to using a
clustered index. One such example is where
>a non-clustered index covers the query.
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message
>news:0d1201c514d6$150fc8c0$a401280a@.phx.gbl...
>
>.
>
|||I wouldn't say "wrong", Pete. But perhaps an over-generalization... :-)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...[vbcol=seagreen]
> Thanks Tibor,
> Ah well, wrong again ;)
> Peter
>
> fast or slow. The optimizer generates
> different ways. And when the execution engine
> influences performance.
> non-clustered indexes" is too much of a
> clustered index will result in fewer I/O
> clustered index. One such example is where
> wrote in message
|||So far over its caused a capsize :-D

>--Original Message--
>I wouldn't say "wrong", Pete. But perhaps an over-
generalization... :-)
>--
>Tibor Karaszi, SQL Server MVP
>http://www.karaszi.com/sqlserver/default.asp
>http://www.solidqualitylearning.com/
>
>"Peter The Spate" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:0d6f01c514e4$afc799c0$a401280a@.phx.gbl...
than[vbcol=seagreen]
a[vbcol=seagreen]
is
>
>.
>
|||Peter, Peter, Peter...
Don't be so hard on yourself.
Don't you remember what Einstein said?
He would rather have a watch that had stopped completely, than one that lost
a second a day.
A watch that had stopped would still show the correct time twice every day.
If you just keep saying that clustered indexes are faster than nonclustered,
you will occasionally be right.
:-)
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Peter The Spate" <anonymous@.discussions.microsoft.com> wrote in message
news:0efe01c51509$798a6890$a401280a@.phx.gbl...[vbcol=seagreen]
> So far over its caused a capsize :-D
>
> generalization... :-)
> wrote in message
> than
> a
> is
|||Mark Wilden wrote:
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23r$pIsQFFHA.2600@.TK2MSFTNGP09.phx.gbl...
> I hope this is apocryphal or metaphorical or just a joke.
> Personally, I'd rather have a watch that lost a second a day.
Well, Einstein rode his bike to work every day because he was concerned
about all the moving parts in a car. He also had many copies of the same
suit to avoid having to make a decision about what suit to wear. He led
a simple life outside and a rather complex one inside. Most of us fall
somewhere in the middle. I'm with you on the watch.
David Gugick
Imceda Software
www.imceda.com
|||"David Gugick" <davidg-nospam@.imceda.com> wrote in message
news:Oc5GKHVFFHA.228@.TK2MSFTNGP15.phx.gbl...
> Well, Einstein rode his bike to work every day because he was concerned
> about all the moving parts in a car. He also had many copies of the same
> suit to avoid having to make a decision about what suit to wear. He led
> a simple life outside and a rather complex one inside.
My favorite worst Einstein quote is the one about making things as simple as
possible, but no simpler. It's not really possible to make things simpler
than possible. You can replace "simple" in that quote with any "good"
quality and have an equally valid (and vacuous) statement.