Function vs Table Join

I import spreadsheets from various departments.  Each has their own way of recording an employees name eg some depts include the employees initial, others dont, some put a full stop have the initial, some record the name as <lastname>, <firstname> and other cases its recorded as <firstname lastname>.  You get the idea.  None of them use their NT login id, but to some of our backend databases - this is the unique identifier.

So I created a table called NameLookup which stores the NT User ID for each possible alias that an employee might have eg:

Alias            NTUserID
Bob Smith    bsmith
Smith, Bob   bsmith
Brian Smith  brsmith
Sue Green   sgreen

In the example in the code snippet there are two possible solutions to the same required query result.  tblPayroll stores its data based on NT User ID.  tblEmployeeSales on the other hand, does not and has one row per day per employee of their total sales for that day..  This is where I need to use the alias in order to join on tblPayroll.

This is just one example.  There are many other tables which need to be joined with NameLookup.

Right now the function method returns just as quickly, if not faster than the JOIN method.  However, I dont have that much data to compare - say 70 active employees each with maybe 3 to 6 aliases.  So as time goes on, this will increase.  Should I expect the function performance to degrade?
Start Free Trial

1:
2:
3:
4:
5:
6:
7:
8:
9:
10:
11:
12:
13:
14:
15:
16:
17:
18:
19:
20:
21:
22:
23:
24:
25:
26:
27:
28:
29:
30:
31:

-- Solution # 1 with JOIN ----------------------------------------------
 
SELECT emp.EmployeeName, pay.Scale, pay.HourlyRate, emp.SalesTotal
  FROM tblPayroll pay
 INNER JOIN (SELECT n.NTUserID, e.EmployeeName, SUM(e.Total) 'SalesTotal'
               FROM tblEmployeeSales e
              INNER JOIN NameLookup n ON n.Alias = e.EmployeeName
              GROUP BY n.NTUserID, e.EmployeeName) Emp ON Emp.NTUSerID = pay.NTUserName
 
-- Solution # 2 with UDF ----------------------------------------------
 
CREATE FUNCTION fnNTID (@NamePass Varchar(50))
 
RETURNS Varchar(50)
 
AS  
BEGIN 
  DECLARE @NTID as Varchar(50)
 
  SELECT @NTID = NTUserID 
    FROM NameLookup
   WHERE Alias = @NamePass
 
  RETURN ISNULL(@NTID, @NamePass)
END
 
 
SELECT emp.EmployeeName, pay.Scale, pay.HourlyRate, SUM(emp.Total) 'SalesTotal'
  FROM tblPayroll pay
 INNER JOIN tblEmployeeSales emp ON dbo.fnNTID(emp.EmployeeName) = pay.NTUserName
 GROUP BY emp.EmployeeName, pay.Scale, pay.HourlyRate