[AccessD] [dba-SQLServer] Indexes on single value fields

Hello John,

Glad that your times have reduced to five seconds, I bet you jumped with joy
at that.

I had previously understood that indexes on fields with binary values were
not beneficial.  But I have to also query my brain to ask was that "Indexes
with an even spread of binary values eg 50 yes and 50 no.

Anyway, I suppose in the end, we all try it one way and then try it another
and pick the one thats best.

I am still curious about your cast as to whether Y and Null is better than 1
and 0 as a bit field.  It should be as fast, otherwise whats the point of
bit.

Thanks

Mark


On 23 June 2010 13:45, jwcolby <jwcolby at colbyconsulting.com> wrote:

> Mark Breen,
>
> I think it was you that mentioned that an index on a field that only
> contained a single value ('Y'
> in my case) would not be used.  I can report that it definitely is used.  I
> have been slowly
> converting my fields with either a 'Y' or a ' ' (space), replacing the ' '
> with null.  I tried to do
> a count / group by to discover how many values I had in one of my fields
> and without the index it
> had not finished after about 8 minutes.  With an index it took five
> seconds.
>
> In fact it took less time to build the index than it took to count without
> the index, which is
> pretty strange if you ask me.
>
> I use these "Y / nothing" fields as criteria in where clauses for the
> orders that the client sends
> me.  I always generate cover indexes on the order selection fields before
> running the order because
> it so dramatically reduces the time to select the resulting order records.
>
> --
> John W. Colby
> www.ColbyConsulting.com
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>