A UDF is a User Defined Function in SQL Server. You can develop the function using T-SQL or your favorite .NET language (C#, VB, etc).
For Part One I’m going to talk about Scalar UDFs. Scalar UDFs are functions that return a single value only. There are a couple limitations to what UDFs can and can’t do. They cannot modify data in tables and dynamic SQL is also not an option.
Besides the function limitations they also have special syntax requirements:
1. They must have a BEGIN and END clause
2. They must be qualified by schema name.
3. You cannot omit parameters even if optional. Those must contain the DEFAULT keyword
So your first question might be “do I use T-SQL or the CLR”? The answer is typically T-SQL is faster for routine set-based computations. However, if you’re dealing with very complex computations the CLR is a better bet.
Let’s write a very simple T-SQL Scalar UDF function:
-- turn off count
SET NOCOUNT ON;
USE AdventureWorks;
-- check if function already exists and drop if so
IF OBJECT_ID('dbo.myUDF', 'FN') IS NOT NULL
DROP FUNCTION dbo.myUDF;
GO
-- declare function specifying an integer as the input and varchar(20) as output
CREATE FUNCTION dbo.myUDF
(@CustomerID AS INT) RETURNS VARCHAR(20)
AS
BEGIN
DECLARE @myResult AS VARCHAR(20);
-- function query
select @myResult = case @CustomerID
when 1 then 'International Business Machines'
when 2 then 'Microsoft'
when 3 then 'Ebay'
else 'Unknown'
END
RETURN @myResult;
END
GO
This generates something like the following:
CustomerName
International Busine
Microsoft
Unknown
Unknown
Unknown
Pretty basic right? So why should you care? Well, Scalar UDFs are easy to write, and easy to maintain. I find them particularly useful for lookups and reporting when you need the same data over and over again.
There are some drawbacks however. (Life couldn’t be that simple could it).
Scalar UDFs when called are run on all the rows of the table and not just on the resultset. This can have a negative impact on performance just like cursors. If you’re dealing with a massive table Scalar UDFs might not be your cup of tea.
So what do you do if you want a neatly packaged function but don’t want the performance impact on a large table?
The answer could be table-valued UDFs as I will show in Part Two [coming soon].
