User defined functions give you great benefits in terms of encapsulation and code reusability. Unfortunately though, when you invoke a scalar UDF as part of a query, and pass the function a column from the table as input, the function is invoked separately for each row. This is true even when all the function has is a RETURN clause with a single expression that theoretically could have been inlined in the query. This is how SQL Server always handled scalar UDFs from the moment those were introduced in the product (version 2000) and still does today (version 2008). The result is that a query that uses such a function would typically run significantly slower than a query that embeds the original expression inline instead of invoking the function.
Learn more from "Inline Conditional Aggregation: One for All" and "Inline vs. Multistatement Table-Valued UDFs."
I’ll first demonstrate the problem and then provide an alternative that would allow you to use functions without sacrificing the performance of your queries.
To demonstrate the problem first create the table T1 and populate it with 1,000,000 rows by running the following code:
-- Create and populate T1 with 1,000,000 rows
SET NOCOUNT ON;
IF OBJECT_ID('dbo.T1') IS NOT NULL DROP TABLE T1;
GO
WITH
L0 AS (SELECT 0 AS c UNION ALL SELECT 0),
L1 AS (SELECT 0 AS c FROM L0 AS A CROSS JOIN L0 AS B),
L2 AS (SELECT 0 AS c FROM L1 AS A CROSS JOIN L1 AS B),
L3 AS (SELECT 0 AS c FROM L2 AS A CROSS JOIN L2 AS B),
L4 AS (SELECT 0 AS c FROM L3 AS A CROSS JOIN L3 AS B),
L5 AS (SELECT 0 AS c FROM L4 AS A CROSS JOIN L4 AS B),
Nums AS (SELECT ROW_NUMBER() OVER(ORDER BY (SELECT 0)) AS n FROM L5)
SELECT n INTO dbo.T1 FROM Nums WHERE n <= 1000000;
GO
Next, create the scalar UDF AddOne by running the following code:
-- Create scalar function AddOne
IF OBJECT_ID('dbo.AddOne') IS NOT NULL DROP FUNCTION dbo.AddOne;
GO
CREATE FUNCTION dbo.AddOne(@n AS BIGINT) RETURNS BIGINT
AS
BEGIN
RETURN @n + 1;
END
GO
This particular function simply adds 1 to the input value, but the idea of course is to use an example for a calculation that can be expressed as a single expression. Just the same the function could have been one that calculates the number of working days between two dates, the last month day corresponding to the input value, or any other single-expression calculation.
Now run the following query which invokes the function:
-- Query 1
SELECT TOP (1) n, dbo.AddOne(n) AS r