Nonclustered Indexes on Bit Flags

  • Hi All

    I have a scenario where I have 3 columns and all 3 of them are used in the where clauses of simple queries or ones having joins .

    TABLE(

    Column1 int

    FLAG1 bit

    FLAG2 bit

    )

    Sample queries :

    Select * from TABLE where FLAG1 =1 and FLAG2 =0

    (Any combination of these flags)

    Select * from TABLE inner join SOMEOTHERTABLE on

    TABLE.Column1 = SOMEOTHERTABLE .Column1

    where FLAG1 =1 and FLAG2 =0

    ( any join and combination of flags)

    Questions :

    What would be the best nonclustered index strategy :

    Column1 as the index key including FLAG1 and FLAG2

    or

    Column1,FLAG1 and FLAG2 in the index key

    Points to note :

    The queries are part of an ETL process and are used to track new records vs old records. The Flags switch states within the same job . So if we are creating an index on all 3 columns, the index has to be reorganized more than once based on the flag states. If we keep them in the include list , then its only about updating the leaf data with the latest flag values.

    On the other hand, an index on all 3 columns will result in an index Seek alone , where as for the included list , there will be an index seek and a predicate .

    Does the predicate cause more overhead than reorganizing the index or is it the opposite ?

  • It's really going to come down to testing to see if making modifications to the key values hurts performance, and if so, how much. Just having an index on the bit columns alone is unlikely to get picked for most queries by the query optimizer just because the statistics are going to be so uninteresting. But, you never know since the selectivity will be... well, not good, but better than nothing if you have multiple bit columns. Nah, it's unlikely to be very useful the more I think about it. However, testing that too won't hurt.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • You'd want the flag column(s) to key the index, not column1. Start with whichever flag is more selective. So, either: (flag1, flag2) or (flag2, flag1).

    But, as Grant noted, it's hard to imagine the selectivity on flag columns being high enough to get SQL to use a nonclus index.

    In theory you could create two separate nonclus indexes, on (flag1, column1) and (flag2, column2) and SQL could seek each separately and join on the results, but it's extremely rare for that to actually happen in reality.

    SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply