SQL Server Performance

Will NC Index help in Self Join?

Discussion in 'SQL Server 2005 T-SQL Performance Tuning' started by atulgoswami, Aug 25, 2009.

  1. atulgoswami New Member

    Hi,
    I have a temp table and fetching multiple values using Self Join on some specific columns.
    OR
    I have a temp table and updating couple of columns in temp table using self join on some specific columns (other than the columns which will be updated).
    Now would Non clustered Index on these columns give any benifit in terms of performance?
    Thanks
  2. FrankKalis Moderator

    I'm not sure I understand you. Can you post a sample?
    Generally indices are beneficial when it comes to data retrieval. They slow down data modification operations. It's up to you to find the right balance here.
  3. atulgoswami New Member

    Create Table #t1
    (
    A_ID INT,
    B_ID INT,
    C_ID INT,
    R_Code VARCHAR(10),
    R_Date DateTime
    )
    Let's assume, i want to fetch max of R_Date among A_ID, B_ID and C_ID based on R_Code, so I am making a self join on #t1 to get this. This query is executed for several R_Code values.
    Now if i put a non clustered index on columns (A_ID, B_ID and C_ID), then would it make any difference?

    I can check this but wondering if anyone come across this.

    I am not sure about how index works incase of self join.
    Thanks for your reply and hope i am making any sense here.
    Thanks

Share This Page