I have a table with about 1 million to 5 million records. Small part of that records have one of bit column set to 'TRUE'. Need to fast find that records. I think that index can speed up searching on this column, but i afraid about INSERTs. Hence my question.
Database is working like sort of data warehouse, so there is many SELECTs and small (up to 10-20 per day) but quite large INSERTs (up 200 thousand records at once). I'm afraid about longer time of that imports to database.
Best Answer
An index on a bit for 1 million records is useless. The optimizer will never ever use it, you'll just pay for maintaining it. A much better alternative is to add this bit as the leftmost key on the clustered index.
But I will make a blind shot in the dark and guess that what you have is a queue pattern: records are dropped in the table with the bit set to 'TRUE' (ie. 'needsprocessing = true') and then a background process looks for these records, does some processing, and updates the bit to FALSE. This is a omnipresent pattern, also affectionately know as the 'performance disaster recipe pattern'. I would recommend dropping the records in to the table and dropping a notification (could be as simple as the newly inserted record ID), at the same time, into a queue. See Using Tables as Queues.