Microsoft SQL Server Desktop Engine - Re: "BY" vs "By" (was Re: Query 10 times slower (and results reordered!) from A

You are viewing an archived Text-only version of the thread. To view this thread in it's original format and/or if you want to reply to this thread please [click here]

Author

Re: "BY" vs "By" (was Re: Query 10 times slower (and results reordered!) from A

Roy Harvey

2006-10-26, 12:15 am

On 25 Oct 2006 15:21:09 -0700, wizofaus@hotmail.com wrote:

>Further on this, I determined that
>
>a) sql server definitely does create an implicit index when creating a
>foreign key constraint (you can see it in the sysindexes table)

No, it does not. It creates index statistics, which is what you were
seeing, but not an index.

>but
>b) adding an explicit index definitely speeds up queries that filter
>out most of the records - by a factor or 4 or 5!

Yes, having an index that fits a selective query is way faster, but
remember this is comparing to no index at all.

>however
>c) adding the explicit index makes no difference to queries that do
>need to process most of the records

The optimizer knows better to use an index - and thus go jumping all
over the table - when it has to look at most of the table in any case.

Roy Harvey
Beacon Falls, CT

Sponsored Links