CK (c_**********@hotmail.com) writes:
I have this UDF
CREATE FUNCTION IsSupervisor (@empID int)
RETURNS bit
AS
BEGIN
DECLARE @retval bit
SET @retval = 'FALSE'
IF EXISTS ( SELECT SupervisorID FROM Supervisors WHERE SupervisorID =
@empID )
SET @retval = 'TRUE'
RETURN @retval
END
This works ok. My question is "is there a better way to write this
function?" Can I just return the results of the Exists statement?
Instead of creating and setting a variable? If there is a row I want to
return true, else return false. Any comments are appreciated.
The best is to not write the function at all, but use the expression
inline. Maybe in some future version there will be inline scalar functions,
but until then, best practice is to avoid dataaccess in UDFs.
Say that you run this query:
SELECT ...
FROM Employees
WHERE dbo.IsSupervisor(EmpId) = 1
SQL Server will have to scan Employees and call the UDF for every single
row.
If you instead write:
SELECT ...
FROM Employees E
WHERE EXISTS (SELECT *
FROM Supervisors S
WHERE E.EmpID = S.SupervisorID)
The optimizer have the choioce of reading the (supposedly) smaller
Supervisor table, and the look up the rows in the Employees table
through the index.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Links for SQL Server Books Online:
SQL 2008:
http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:
http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000:
http://www.microsoft.com/sql/prodinf...ons/books.mspx