I have two tables.
EmployeeCode int Primary Key
Primay key on (Employeecode, StockCode)..
There is no foreign key relation between these 2 tables.
Now my question is which query give more performance. and why?
1. Select * from
Employee INNER JOIN Employee_Stock on Employee.Employeecode = Employee_Stock.EmployeeCode
2. Create a foreign Key between Employee and Employee_Stock for EmployeeCode. and run the same query.
Actually we forgot to put the foreign key relationship between these 2 tables and we have lot of queries joining them..
Now if we add foreignkey, is it going to improve the performance or not?
Foreign keys enforce business rules and relational integrity, but in themselves do not increase performance. In some cases they may actually decrease performance while the database maintains relational integrity during inserts, updates, and deletes. You should still establish them to save you all sorts of headaches later when you find that your data does not make sense.
If you have already have indexes on your joined columns then I don't think your query is about as efficient as it is going to get. Show a query plan and verify that the indexes are being used.
If it's not practically useful, then it's practically useless.