Post reply

Index using Include for Join

  • November 17, 2011 at 4:37 am

    #246980

    Hi All

    Here i am little bit confuse i have query with join condition and i need to apply an index for this query so can anyone say me how to apply index on query with join statement

    Select T1.Col1,T1.Col2,T2.Col1,T2.Col2

    From Table1 T1

    Inner Join Table2 T2 on T2.Col1 = T1.Col1

    now what should i do to put index over this query

    Option one

    Do i need to create two seprate index for table1 and table2 like

    Create index in1 on table1(col1) include (col2)

    Create index in2 on table2(col1) include (col2)

    Or

    There is anyway where i can mix them together and run it.

    Thanks & Regards
    Syed Sami Ur Rehman
    SQL-Server (Developer)
    Hyderabad
    Email-sami.sqldba@gmail.com

  • November 17, 2011 at 5:01 am

    #1409089

    There's no way to answer that question. There are too many variables than affect the answer.

    The real problem with that query is that you don't filter anything, so the likelier execution plan is to scan both tables. In that case an index will likely be scanned rather than seeked.

    Everything I've said here as exceptions, so you need to see the execution plan to really see what's going on.

  • November 17, 2011 at 5:14 am

    #1409094

    Hi Ninja's_RGR'us

    Yes your correct but its just an example i know we need have filter i just want to ask how to create index over the query which have joins do i need to create sepreate index for each table or there is any way where one index will work for both the tables.

    Thanks & Regards
    Syed Sami Ur Rehman
    SQL-Server (Developer)
    Hyderabad
    Email-sami.sqldba@gmail.com

  • November 17, 2011 at 5:16 am

    #1409096

    sami.sqldba (11/17/2011)

    i just want to ask how to create index over the query which have joins do i need to create sepreate index for each table or there is any way where one index will work for both the tables.

    Indexes consist of one of one or more columns from a single table.

    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

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

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