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

Mark,

I think that you can't index a binary because it is a bit in an integer and SQL Server treats it 
differently than regular fields.

 >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".

As I understand it is that this is also true.  ANY index only gets used if you are selecting a value 
or values with a small percent of the total values.

In my case this is almost always true.  There is always a T or nothing.  The T values are almost 
always a small percent of the total, only 5 or 10 percent.  I do have one specific field that has 
about 40% T though.  That would be a good test to see if it uses the index.

John W. Colby
www.ColbyConsulting.com


Mark Breen wrote:
> 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
>>
>>