How optimal is this function

Question

I'm about to implement this function to calculate some numbers.

CREATE FUNCTION [dbo].[funLookupFTE] (@PFID int) RETURNS
    VARCHAR(20) AS BEGIN 

DECLARE @NumberOfFTE AS VARCHAR(20)
SET @NumberOfFTE = (SELECT  SUM(CASE WHEN Hours <= 20 THEN 0.5 WHEN Hours > 20 THEN 1     END) AS FTECount 
                    FROM tblPractitioners 
                    WHERE PFID =
@PFID)
RETURN @NumberOfFTE
END

Just trying to see if it's optimal and it won't take matters into its own hand.

Thanks

Answer 1

If this is to be called in any sort of query, you're probably better off using a subquery instead of a user-defined function.

For example:

SELECT * FROM tblPractioners P1
INNER JOIN
(
    SELECT  PFID, SUM(CASE WHEN Hours <= 20 THEN 0.5 WHEN Hours > 20 THEN 1 END) 
        AS FTECount 
    FROM tblPractitioners 
    GROUP BY PFID
) P2 ON P1.PFID = P2.PFID

Performance of user-defined functions can be very poor, but you'll really need to compare the execution plans using a subquery or UDF to see which is better.