Post reply

Multiple Indexes

  • December 15, 2007 at 11:40 am

    #119751

    I have a moderately large table (just under 1 million rows) which currently has only one index on it, a unique non-clustered index over 3 columns. This index was created automatically by the server to enforce the unique constraint placed on that combination of those 3 columns. One of the columns in particular is heavily queried against in where clauses and used as a join condition in a one to many relationship (it is the many side).

    Would it be beneficial to create an index specifically on that column, or does its presence as one of the 3 columns in a multicolumn index handle that situation?

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

  • December 16, 2007 at 5:03 am

    #760576

    Before all: I really recommend you to put a CLUSTERED index onto all of your tables.

    In this particular case, if the heavily queried column is the member of the composite index, and the composite index is only for enforce uniqueness, I'd do the following: I'd create a clustered uniqe index on that three column, with the queried column on the first place and drop the existing nonclustered index. This way the index would be effectively used by queries returning more rows.

    Beware of the order of the columns: create unique clustered index ix_indexname on schema.tablename (col1, col2, col3) is different from an index with (col3, col1, col2) columns.

    -- Erik http://blog.rollback.hu

  • December 16, 2007 at 5:13 am

    #760577

    Is the column in question the first column in that composite index? If yes, an index on it alone will be redundant. If no, then create it.

    I'll second the comment about the cluster. Also, the table has no primary key?

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass

  • December 16, 2007 at 5:30 pm

    #760606

    Thank you all for the advice. I'll replaced the nonclustered index with a clustered index and put the heavily queried column first.

    ---
    Timothy A Wiseman
    SQL Blog: http://timothyawiseman.wordpress.com/

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

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