Does adding a non clustered index to foreign key speed up the join?



  • I have 2 large tables.

    Sample query:

    SELECT USERNAME,S.ProductName,S.Amt,S.Date
    FROM USERS U
    JOIN SALES S
    ON U.ID=S.UID
    

    Presently the Users.ID and Sales.ID are clustered indexes.

    So as to speed up the JOIN, will it help if I add a non-clustered index on Sales.UID? And add the following columns as included column: ProductName, Amt and Date?



  • Even if it doesn't speed up that query an index on the foreign key will help if you ever delete a user, or select a the sales of a single user. eg

    SELECT USERNAME,S.ProductName,S.Amt,S.Date
    FROM USERS U
    JOIN SALES S
    ON U.ID=S.UID
    WHERE U.ID = @user
    

    So indexing foreign key columns is generally recommended.




Suggested Topics

  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2
  • 2