Which one is faster? Inner Join or Function
Is it good to write Inner join with all the 4 table or Write a Select Statement from Employment Data table with function calling from all other table
Option 1 :
Select ED.ECode, EM.EName, ED.DCode, DM.DName, ED.LCode, LM.LName
FROM Employment_Data ED,
Employee EM,
Department DM,
Location LM
WHERE ED.ECode = EM.ECode
AND ED.DCode = DM.DCode
AND ED.LCode = LM.LCode
Option 2 :
Select ED.ECode, dbo.Emp_Name(ED.ECode),
ED.DCode, dbo.Dep_Name(ED.DCode),
ED.LCode, dbo.Loc_Name(ED.LCode)
FROM Employment_Data ED
Which one is faster?
For a specific answer to your question, you will need to test both methods against your current dataset. I would also advise creating a larger test dataset to test how each scale as the dataset grows.
I would also recommend writing your JOINs in an easier to read format specifying the INNER JOIN and the ON:
SELECT ED.ECode,
EM.EName,
ED.DCode,
DM.DName,
ED.LCode,
LM.LName
FROM Employment_Data ED
INNER JOIN Employee EM ON ED.ECode = EM.ECode
INNER JOIN Department DM ON ED.DCode = DM.DCode
INNER JOIN Location LM ON ED.LCode = LM.LCode
The proposed syntax in the question suggests you are thinking of implementing this using scalar functions that perform data access. This is a pattern you should avoid at all costs. SQL Server will end up running a separate query (to simplify slightly) for each function, for each row. As you might imagine, this is not a recipe for high performance. In addition, the query optimizer has essentially zero opportunity to rewrite such queries for better performance.
Using a join is the relational solution, and will allow the optimizer to apply the tricks it knows. To emphasise: you will almost never want to use scalar functions where they might be called for more than a very small number of rows, as they are currently implemented. Functions that perform data access should be avoided even more than those that do not.