Post reply

Question on Index with Included columns

  • January 11, 2013 at 9:57 am

    #272726

    I'm planning to create an index with included columns but I've a doubt whether it will work or not. TableABC is inner joined to tableXYZ, since XYZ's columns are also involved in the SELECT list, so I'm not sure if the new index with included columns will work or not.

    SELECT abc.columnA,

    abc.columnB,

    abc.columnC,

    xyz.columnX,

    xyz.columnY

    FROM TableABC abc (nolock)

    INNER JOIN (select columnX, columnY, columnZ

    from dbo.TableXYZ (nolock)

    ) xyz

    ON xyz.columnX = abc.columnD

    WHERE abc.columnC>='somevalue' and

    abc.columnE = 'somevalue'

    So, would an index like the following work:

    CREATE NONCLUSTERED INDEX [Indexname] on [TableABC]

    ([ColumnC],[ColumnE])

    INCLUDE

    ([ColumnA], [ColumnB],[ColumnC])

  • January 11, 2013 at 10:14 am

    #1576418

    You can't have a column in the key and include. One or the other.

    Given the predicates, switch the order of the key columns, it's not efficient for that query as you have it.

    Watch those nolocks. See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    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

  • January 11, 2013 at 10:25 am

    #1576426

    GilaMonster (1/11/2013)

    You can't have a column in the key and include. One or the other.

    Given the predicates, switch the order of the key columns, it's not efficient for that query as you have it.

    Watch those nolocks. See - http://blogs.msdn.com/b/davidlean/archive/2009/04/06/sql-server-nolock-hint-other-poor-ideas.aspx

    Thanks Gail. I'll keep your suggestions in mind.

    Also, since tableXYZ columns are also involved in the SELECT list, would the index be useful?

  • January 11, 2013 at 10:34 am

    #1576434

    For table ABC, probably. Not going to do much for the operations against XYZ of course.

    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

  • January 11, 2013 at 10:37 am

    #1576435

    GilaMonster (1/11/2013)

    For table ABC, probably. Not going to do much for the operations against XYZ of course.

    Thank you, Gail.

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

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