SQL - User-Defined Functions (UDF)
SQL Server supports three primary UDF types: scalar functions, inline table-valued functions (iTVF), and multi-statement table-valued functions (mTVF). Each type has specific performance...
Key Insights
- User-Defined Functions encapsulate reusable SQL logic into named operations that accept parameters and return values, eliminating code duplication across queries and stored procedures
- Scalar functions return single values while table-valued functions return result sets, each serving distinct use cases in query optimization and data transformation
- Inline table-valued functions generally outperform multi-statement functions because the query optimizer can integrate them directly into execution plans rather than treating them as opaque operations
Understanding UDF Types
SQL Server supports three primary UDF types: scalar functions, inline table-valued functions (iTVF), and multi-statement table-valued functions (mTVF). Each type has specific performance characteristics and appropriate use cases.
Scalar functions return a single value of any SQL data type. They’re useful for calculations, formatting, and data validation:
CREATE FUNCTION dbo.CalculateTax
(
@Amount DECIMAL(18,2),
@TaxRate DECIMAL(5,4)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
RETURN @Amount * @TaxRate
END
GO
-- Usage
SELECT
OrderID,
TotalAmount,
dbo.CalculateTax(TotalAmount, 0.0825) AS TaxAmount
FROM Orders
Inline table-valued functions return a table result set defined by a single SELECT statement:
CREATE FUNCTION dbo.GetCustomerOrders
(
@CustomerID INT,
@StartDate DATE
)
RETURNS TABLE
AS
RETURN
(
SELECT
OrderID,
OrderDate,
TotalAmount
FROM Orders
WHERE CustomerID = @CustomerID
AND OrderDate >= @StartDate
)
GO
-- Usage
SELECT *
FROM dbo.GetCustomerOrders(1001, '2024-01-01')
WHERE TotalAmount > 500
Multi-statement table-valued functions use explicit table variable declarations and multiple statements:
CREATE FUNCTION dbo.GetOrderSummary
(
@Year INT
)
RETURNS @Summary TABLE
(
Month INT,
OrderCount INT,
TotalRevenue DECIMAL(18,2)
)
AS
BEGIN
INSERT INTO @Summary
SELECT
MONTH(OrderDate),
COUNT(*),
SUM(TotalAmount)
FROM Orders
WHERE YEAR(OrderDate) = @Year
GROUP BY MONTH(OrderDate)
UPDATE @Summary
SET TotalRevenue = ISNULL(TotalRevenue, 0)
RETURN
END
GO
Performance Considerations
Scalar UDFs historically suffered severe performance penalties in SQL Server versions prior to 2019 due to row-by-row execution and lack of parallel processing. SQL Server 2019 introduced scalar UDF inlining, which automatically converts eligible scalar functions into relational expressions.
To enable scalar UDF inlining:
-- Database level
ALTER DATABASE SCOPED CONFIGURATION
SET TSQL_SCALAR_UDF_INLINING = ON;
-- Function level (opt-out)
CREATE FUNCTION dbo.CalculateDiscount
(
@Price DECIMAL(18,2)
)
RETURNS DECIMAL(18,2)
WITH INLINE = OFF -- Prevents inlining
AS
BEGIN
RETURN @Price * 0.10
END
For functions that cannot be inlined, consider refactoring into inline table-valued functions or using computed columns:
-- Instead of scalar UDF
CREATE FUNCTION dbo.GetFullName_Slow
(
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50)
)
RETURNS NVARCHAR(101)
AS
BEGIN
RETURN @FirstName + ' ' + @LastName
END
GO
-- Use iTVF for better performance
CREATE FUNCTION dbo.GetFullName_Fast
(
@FirstName NVARCHAR(50),
@LastName NVARCHAR(50)
)
RETURNS TABLE
AS
RETURN
(
SELECT @FirstName + ' ' + @LastName AS FullName
)
GO
-- Or use CROSS APPLY
SELECT
e.EmployeeID,
fn.FullName
FROM Employees e
CROSS APPLY dbo.GetFullName_Fast(e.FirstName, e.LastName) fn
Practical Implementation Patterns
Data Validation and Cleansing
UDFs excel at standardizing data transformations across your database:
CREATE FUNCTION dbo.CleanPhoneNumber
(
@Phone NVARCHAR(20)
)
RETURNS NVARCHAR(20)
AS
BEGIN
DECLARE @Cleaned NVARCHAR(20)
-- Remove non-numeric characters
SET @Cleaned = @Phone
SET @Cleaned = REPLACE(@Cleaned, '(', '')
SET @Cleaned = REPLACE(@Cleaned, ')', '')
SET @Cleaned = REPLACE(@Cleaned, '-', '')
SET @Cleaned = REPLACE(@Cleaned, ' ', '')
SET @Cleaned = REPLACE(@Cleaned, '.', '')
-- Format as (XXX) XXX-XXXX
IF LEN(@Cleaned) = 10
SET @Cleaned = '(' + SUBSTRING(@Cleaned, 1, 3) + ') ' +
SUBSTRING(@Cleaned, 4, 3) + '-' +
SUBSTRING(@Cleaned, 7, 4)
RETURN @Cleaned
END
GO
Complex Business Logic
Encapsulate multi-step calculations that involve business rules:
CREATE FUNCTION dbo.CalculateShippingCost
(
@Weight DECIMAL(10,2),
@Distance INT,
@ShippingClass VARCHAR(20)
)
RETURNS DECIMAL(10,2)
AS
BEGIN
DECLARE @BaseCost DECIMAL(10,2) = 5.00
DECLARE @WeightCost DECIMAL(10,2)
DECLARE @DistanceCost DECIMAL(10,2)
DECLARE @ClassMultiplier DECIMAL(5,2)
-- Weight calculation
SET @WeightCost = CASE
WHEN @Weight <= 5 THEN 0
WHEN @Weight <= 20 THEN (@Weight - 5) * 0.50
ELSE 7.50 + ((@Weight - 20) * 0.75)
END
-- Distance calculation
SET @DistanceCost = @Distance * 0.10
-- Class multiplier
SET @ClassMultiplier = CASE @ShippingClass
WHEN 'Express' THEN 2.0
WHEN 'Priority' THEN 1.5
WHEN 'Standard' THEN 1.0
ELSE 1.0
END
RETURN (@BaseCost + @WeightCost + @DistanceCost) * @ClassMultiplier
END
GO
Dynamic Filtering with iTVFs
Inline table-valued functions provide parameterized views with excellent performance:
CREATE FUNCTION dbo.FilterProducts
(
@CategoryID INT = NULL,
@MinPrice DECIMAL(10,2) = NULL,
@MaxPrice DECIMAL(10,2) = NULL,
@InStock BIT = NULL
)
RETURNS TABLE
AS
RETURN
(
SELECT
ProductID,
ProductName,
CategoryID,
Price,
StockQuantity
FROM Products
WHERE (@CategoryID IS NULL OR CategoryID = @CategoryID)
AND (@MinPrice IS NULL OR Price >= @MinPrice)
AND (@MaxPrice IS NULL OR Price <= @MaxPrice)
AND (@InStock IS NULL OR
(@InStock = 1 AND StockQuantity > 0) OR
(@InStock = 0 AND StockQuantity = 0))
)
GO
-- Flexible querying
SELECT * FROM dbo.FilterProducts(DEFAULT, 10.00, 50.00, 1)
ORDER BY Price
Error Handling and Best Practices
Always implement error handling in multi-statement functions:
CREATE FUNCTION dbo.SafeDivide
(
@Numerator DECIMAL(18,2),
@Denominator DECIMAL(18,2)
)
RETURNS DECIMAL(18,2)
AS
BEGIN
IF @Denominator = 0
RETURN NULL
RETURN @Numerator / @Denominator
END
GO
Use schema binding to prevent underlying table modifications:
CREATE FUNCTION dbo.GetActiveCustomers()
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN
(
SELECT
CustomerID,
CustomerName,
Email
FROM dbo.Customers -- Must use two-part naming
WHERE IsActive = 1
)
GO
Avoid these common pitfalls:
- Don’t call scalar UDFs in WHERE clauses on large tables without inlining
- Avoid non-deterministic functions (GETDATE(), NEWID()) in indexed computed columns
- Don’t nest UDFs excessively; flatten logic when possible
- Never perform data modifications (INSERT/UPDATE/DELETE) within functions
UDFs provide powerful abstraction mechanisms when used appropriately. Prefer inline table-valued functions for set-based operations, leverage scalar UDF inlining in SQL Server 2019+, and always profile performance impact in production-like environments before deploying complex function logic.