Post reply

creating an index on multiple table

  • November 17, 2011 at 3:18 am

    #250765

    Can anyone provide me an information for creating an index for the below mention query .

    Thank you .

    SELECT A.EmpDegree,B.ProjectName,C.HireDate FROM dbo.EmployeeQualification A INNER JOIN

    dbo.EmployeeJobStatus B ON A.EmpID=B.EmpID LEFT OUTER JOIN dbo.EmployeePersonalDetail C

    ON A.EmpID=C.EmpID WHERE @name=C.EmpName

    Here A,B,C are different alias for the table dbo.EmployeeQualification ,dbo.EmployeeJobStatus and

    dbo.EmployeePersonalDetail .

    I exactly want to know how to create an index to multiple colums of different tables .

  • November 17, 2011 at 3:28 am

    #1409057

    An index consists of one or more columns from a single table only.

    For that query I suggest an index on EmployeePersonalDetail (EmpName. EmpID)

    Is EmpID the primary key of all tables?

    p.s. Please if you're going to alias, use meaningful aliases. The query as written is not easy to read. Aliasing EmployeePersonalDetail as C just doesn't make sense. PD or EPD would be a far more meaningful alias

    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

  • November 17, 2011 at 3:54 am

    #1409066

    GilaMonster (11/17/2011)

    An index consists of one or more columns from a single table only.

    Unless you set up an Indexed View, but I suspect am sure it wouldn't help at all in this case.

    -- Gianluca Sartori

  • November 17, 2011 at 4:02 am

    #1409067

    SELECT EQ.EmpDegree, EJS.ProjectName, EPD.HireDate FROM dbo.EmployeeQualification EQ

    INNER JOIN dbo.EmployeeJobStatus EJS ON EQ.EmpID=EJS.EmpID LEFT OUTER JOIN dbo.EmployeePersonalDetail EPD ON EQ.EmpID=EPD.EmpID WHERE @name=EPD.EmpName

    EQ - EmployeeQualification

    EJS - EmployeeJobStatus

    EPD - EmployeePersonalDetail

    Yes EmpID is a primary key

  • November 17, 2011 at 4:04 am

    #1409069

    In that case my indexing suggestion stands and should be sufficient.

    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 5 posts - 1 through 4 (of 4 total)

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