clustered Vs NonClustered indexes

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,
JoeClustered Index Design Guidelines
http://msdn2.microsoft.com/en-us/library/ms190639.aspx
Nonclustered Index Design Guidelines
http://msdn.microsoft.com/library/e...ar_da2_1tbn.asp
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"jaylou" <jaylou@.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
>|||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
"jaylou" <jaylou@.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
>|||On Fri, 16 Dec 2005 12:38:02 -0800, jaylou
<jaylou@.discussions.microsoft.com> wrote:
>I was looking for some info on the major differences between a clustered an
d
>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?|||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
"jaylou" <jaylou@.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
>