clustered Vs NonClustered indexes

16 Dec 2005 8:38 PM

jaylou

I was looking for some info on the major differences between a clustered and
a non-clustered index, but I couldn't find anything in the MS knowledgebase.

Which is more efficient? I am creating reports on an audit table that has
approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
queries take for ever to run. If I changed the indexes to clustered will
that speed things up or will it make no difference?
TIA,
Joe

16 Dec 2005 8:46 PM

Mike Epprecht (SQL MVP)

Clustered Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms190639.aspx

Nonclustered Index Design Guidelines
http://msdn.microsoft.com/library/en-us/architec/8_ar_da2_1tbn.asp

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: m***@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

Show quote

"jaylou" <jay***@discussions.microsoft.com> wrote in message
news:DC221C09-B1F5-47B3-92EC-BF677B1F4017@microsoft.com...
>I was looking for some info on the major differences between a clustered
>and
> a non-clustered index, but I couldn't find anything in the MS
> knowledgebase.
>
> Which is more efficient? I am creating reports on an audit table that has
> approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
> queries take for ever to run. If I changed the indexes to clustered will
> that speed things up or will it make no difference?
> TIA,
> Joe
>

16 Dec 2005 8:53 PM

JT

Efficient for reporting queries? I have found it is particularly beneficial
to have a clustered index that compliments the same sort order of the query.
It also helps if the first column of the clustered index is included in the
where clause.

select
    state, product category, dateofsale, price
from
    SALES
where
    state = 'FL'
order by
    state, product category, dateofsale

Try creating a clustered index on: state, product category, dateofsale

Show quote

"jaylou" <jay***@discussions.microsoft.com> wrote in message
news:DC221C09-B1F5-47B3-92EC-BF677B1F4017@microsoft.com...
>I was looking for some info on the major differences between a clustered
>and
> a non-clustered index, but I couldn't find anything in the MS
> knowledgebase.
>
> Which is more efficient? I am creating reports on an audit table that has
> approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
> queries take for ever to run. If I changed the indexes to clustered will
> that speed things up or will it make no difference?
> TIA,
> Joe
>

16 Dec 2005 10:03 PM

jxstern

On Fri, 16 Dec 2005 12:38:02 -0800, jaylou
<jay***@discussions.microsoft.com> wrote:

>I was looking for some info on the major differences between a clustered and
>a non-clustered index, but I couldn't find anything in the MS knowledgebase.
>
>Which is more efficient? I am creating reports on an audit table that has
>approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
>queries take for ever to run. If I changed the indexes to clustered will
>that speed things up or will it make no difference?

Indexed separately or together?

How many rows is returned by each query?

If you want any informed answers here, posting at least the select
statements (and what fields you've indexed) is going to be helpful.

In general, it's unlikely that clustered/unclustered by itself is
going to affect your runtimes much.

J.

ps - how long is "for ever"?

pps - are other people updating the table at the same time you're
running reports?

17 Dec 2005 1:50 AM

Hilary Cotter

Clustered indexes are best used when you need to return data ordered
according to a criteria, in other words a range of data. Non-clustered
indexes are best used for singleton fetches, or data which is not
necessarily contiguous.

--
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html

Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com

Show quote

"jaylou" <jay***@discussions.microsoft.com> wrote in message
news:DC221C09-B1F5-47B3-92EC-BF677B1F4017@microsoft.com...
>I was looking for some info on the major differences between a clustered
>and
> a non-clustered index, but I couldn't find anything in the MS
> knowledgebase.
>
> Which is more efficient? I am creating reports on an audit table that has
> approx 20,000,000 rows in it. I indexed 3 columns as non clustered and my
> queries take for ever to run. If I changed the indexes to clustered will
> that speed things up or will it make no difference?
> TIA,
> Joe
>