Posted by Erland Sommarskog on 12/07/05 00:41

serge (sergea@nospam.ehmail.com) writes:
> First let me create a function that takes the EmployeeID
> as the input parameter and returns the Employee's
> First and Last name:
>
> CREATE FUNCTION dbo.GetEmployeeName(
> @EmployeeID INT
> )
> RETURNS VARCHAR(100)
> AS
> BEGIN
>
> DECLARE @NAME VARCHAR(100)
>
> SELECT @NAME = FirstName + ' ' + LastName
> FROM Employees
> WHERE EmployeeID = @EmployeeID
>
> RETURN ISNULL(@NAME, '')
>
> END

Don't do this! Writing a scalar UDF to retrieve data is bad usage of the
powers of SQL. If you put the retrieval directly into the query, the
optimizer can find the best way to evaluate the query. This cannot happen
when you put it into a scalar UDF.

Overall, in SQL 2000 there is quite an overhead for calling a UDF. This
has been improved in SQL 2005, which is good for UDFs that do not do
data retrieval.

Theoretically, you could this as an inline UDF:

RETURNS (SELECT FirstName + ' ' Lastname ...)

But, alas, SQL Server does not have inline scalar UDF, so this does
make things any faster.



--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]