asked
Which one is Best either using inner Join or function?
Hi to all .. I've written two different queries as follows First One SELECT VT.VEHICLE_TYPE_NAME FROM VEHICLE_MASTER VM INNER JOIN VEHICLE_TYPE VT ON VT.VEHICLE_TYPE_SNO = VM.VEHICLE_TYPE_SNO Second One SELECT (SELECT DBO.GET_VEHICLE_TYPE_NAME(VM.VEHICLE_TYPE_SNO)) FROM VEHICEL_MASTER VM My function is follows ALTER FUNCTION [DBO].[GET_VEHICLE_TYPE_NAME] ( @VEHICLE_TYPE_SNO AT_INT = NULL ) RETURNS VARCHAR(100) AS BEGIN RETURN (SELECT VT.VEHICLE_TYPE_NAME FROM VEHICLE_TYPE VT WHERE VT.VEHICLE_TYPE_SNO = @VEHICLE_TYPE_SNO) END GO HERE I'VE TO KNOW PERFORMANCE WISE EITHER INNER JOIN IS BEST OR PREDEFINED FUNCTION IS BEST PLEASE HELP ME THANKS TO ALL..........
Inner Join. The function will be called for every row in VEHICLE_MASTER, whereas the join will be one operation that returns a set of data. Be aware that the execution plan may show that the function query is 'better', but this will be based on an assumption that the function is called once, returning one row.
To add to @kev riley's answer, you could rewrite your function to be a table value function, calling this using CROSS APPLY. This would perform just as fast as the inner join. You would change the code to return a table, keeping the select the same as you have now, so it is an easy modification to make.
If we compare between scalar value function and table function for requirement then table value function is good for performance point of view but for your requirement I go with join because any how through table value function also we are making join with main table then why not we directly join with main table. So for your requirement Inner join will be faster.
