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

Mark,

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

I think what happens is that if you have for example 32 "true / false" fields in a table and you 
specify bit, SQL Server "packs" those into a single integer instead of many different char(1) 
fields.  Thus storage is reduced, but at the expense of no ability to index them.

I am not worried about the storage issue, and I absolutely need to be able to index them, so I just 
use a Char(1) to store these fields in my DB from hell.  I have somewhere around 450 of this kind of 
field, that has a 'Y' or a space, and I am in the process of converting that space to a null.  And 
boy is that process slooooow.  About 1/2 hour per field for 50 million records, and I have about 420 
fields left to go.

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