Post reply

Index Tuning

  • January 7, 2008 at 4:28 pm

    #765541

    Clustered index must be the one used for range selections.

    I really doubt there is any case when you select empid BETWEEN @a AND @B.

    So, there is no point to have clustered index on empid.

    But if you need "all eployees from the department(s)" then you've got the range and the reason for clustered index.

    In SQL terms it will be:

    Ind1 on (dept id, empid) (clustered)

    Ind2 on (empid) (Non clustered)

    This structure will involve bookmark lookup for queries having "WHERE empid = @empid" but because it's a single row lookup the cost of it will be insignificant.

    But I would suggest to rermove deptid from emp table. It's not good to mix entities.

    It must be separate table having only (deptid, empid) columns with proper indexes.

    Then you can have 2 clustered indexes, optimizer will choose which one to use for each particular query.

    _____________
    Code for TallyGenerator

Viewing post 16 (of 15 total)

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