February 14, 2007 at 9:18 am
Hi,
I have inherited a database in which a table is having a nonclustered index on a bit field (active) and integer field (TradeCode).
All queries have distinct in it to avoid duplication, which is causing performance issues. When ever I use this table in a join its returns duplicate. To me it looks that the is index is not getting updated properly , if I use INDEX(0) to ignore index I get no duplicates. I started doing reindexing every night, it helps but as day goes by I start getting duplicates.
Has anyone experienced similar behaviours with bit field in an index? I am planning to remove the bit field from index (since its useless to add bit field in an index) but kind of holding since this application was developed by a consultant (yes, u guessed it right, he has disappeared from the face of the earth.)
What you guys say….
Cheers
Deepak
February 14, 2007 at 9:48 am
There are quite a few fixes for indicies on columns with a datatype of bit.
Do you have Service Pack 4 installed ?
SQL = Scarcely Qualifies as a Language
February 15, 2007 at 4:23 am
Thanks Carl, It was index with bit field plus Parallel Query causing the issue. As mentioned in the KB article http://support.microsoft.com/kb/824018/
For the time being I have changed the index key and things are fine.
Cheers
Deepak
February 15, 2007 at 8:53 am
"since its useless to add bit field in an index".
Do you have any support for that bold statement?
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply