Post reply

covering index

  • September 2, 2004 at 9:20 am

    #360855

    Hi,

    When you are creating a convering index, you cannot include datatypes that do not support indexing (specifically in this case 'bit').

    1. Does it make sense to use tinyint to store bit/boolean data in order to add to the covering index?

    2. If the bit column is included in a WHERE clause along with the other columns, will it use the convering index or will it revert to a table/index scan?

    3. I realize both are the tinyint and bit are the same size and irrelevant to SQL Server performance, but in order to have logical schema/code I would rather have datatypes support the information that is in contained in them.

    Thanks

  • September 3, 2004 at 2:52 am

    #521608

    Hi maxismclaren,

    In general: no, this does not make sense. An index is usefull if it is made out of column(s) with as selctive possible values. The summum is a unique index, in which all values are different. A bit/boolean column can have ony 2 different values so is not very distinctive.

    However, you do mention that your goal is to create a covering index, in which case it can make sense, because it means that the database engine doesn't have to perform lookups in the table when the index can be used.

    Another case in which such an index can be usefull is when the values are divided very uneven. Consider for example a table "child" with a bit (must be tinyint to be used in index) column "likes_icecream". It is very likely that teh column will contain "true" for 99% or more of the children. When selecting children that DON'T like ice cream the index can be usefull.

    Regarding your second question: not all columns of an index need to be included in the where clause for the optimizer to consider using the index. The first column however must be included and with each following column the index can be used more efficient.

    I appreciate your wish to use the most adequate data type. You could use a computed column to use in your index, but there are some restrictions to that too.

    Cheers,

    Henk

  • September 3, 2004 at 12:16 pm

    #521706

    maxismclaren,

    Please note that you _can_ include a column with the BIT datatype in an index; you just can't do it from the Enterprise Manager UI.

    --
    Adam Machanic
    whoisactive

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

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