A User-Defined Function (UDF) is a routine that accepts parameters, performs an action, and returns the result of that action as a value.
They allow us to perform calculations, transformations, and complex operations in a modular way, which improves the readability and maintainability of our T-SQL code.
Functions are designed to be integrated directly within other queries. The philosophy is “Write it once, use it many times”.
There are mainly two types you need to master: Scalar and Table-Valued.
Scalar Functions
They are identical in concept to UPPER() or ABS(). They receive one or more parameters and return a single value (a number, a date, text…).
The syntax is as follows,
CREATE FUNCTION dbo.FunctionName
(
@Parameter DataType
)
RETURNS ReturnDataType
AS
BEGIN
DECLARE @Result ReturnDataType;
-- Calculation logic
SET @Result = ...;
RETURN @Result;
END
Practical Example: Price with VAT
Imagine you have to calculate the price with VAT in 50 different reports. Instead of writing Price * 1.21 everywhere (and having to change it if taxes go up), you create a function.
CREATE FUNCTION dbo.ufn_CalculatePriceWithVAT
(
@BasePrice DECIMAL(10, 2)
)
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @FinalPrice DECIMAL(10, 2);
-- Assume a VAT of 21%
SET @FinalPrice = @BasePrice * 1.21;
RETURN @FinalPrice;
END
To use it, it is mandatory to use the schema (usually dbo.) before the name:
SELECT
Name,
Price,
dbo.ufn_CalculatePriceWithVAT(Price) AS FinalPrice
FROM Products;
Scalar functions in older versions of SQL Server (and still today if overused) can be slow because they execute row by row and prevent parallelism.
Use them for simple calculations, but don’t overuse them either. Avoid accessing tables (doing SELECT) inside a scalar function if you are processing millions of rows and can solve it another way.
Table-Valued Functions
These functions do not return a single piece of data, but an entire table. They are like a “View with Parameters”.
We will focus on Inline TVFs, which are the fastest.
The syntax is as follows,
CREATE FUNCTION dbo.ufn_GetOrdersByCustomer
(
@CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
SELECT OrderID, Date, Total
FROM Orders
WHERE CustomerID = @CustomerID
);
They don’t have BEGIN...END. They simply return the result of a query.
To use them, since they return a table, we use them in the FROM or JOIN clause, (not in the SELECT).
SELECT Orders.OrderID, Orders.Total
FROM dbo.ufn_GetOrdersByCustomer(5) AS Orders -- It acts like a table!
WHERE Orders.Total > 100;
This is much cleaner and more modular than writing the WHERE repeatedly in every query.
Limitations of UDFs
To maintain integrity and determinism, SQL Server imposes strict rules on functions:
- No Side Effects: A function cannot change the state of the database. No
INSERT,UPDATEorDELETEto permanent tables. - Non-Determinism: In the past you couldn’t use
GETDATE()orNEWID()inside a function because they return different values each time. (In modern versions this has been relaxed a bit, but it remains a conceptual limitation). - Error Handling: You cannot use
TRY...CATCHinside a function. If it fails, the entire query stops.
Function or Stored Procedure?
Although they are similar, they have very different purposes and rules.
| Characteristic | Stored Procedure (SP) | Function (UDF) |
|---|---|---|
| Goal | Execute a complete business logic (Process). | Calculate and transform values. |
| Invocation | With EXEC (independent). | Inside a SELECT, WHERE, JOIN. |
| Return | May return nothing, OUTPUT parameters, or multiple tables. | Must return a value (scalar or table). |
| Modify Data | YES. Can do INSERT, UPDATE, DELETE. | NO. Can only read. Cannot modify base tables. |
| Try/Catch | Allowed. | Not allowed inside the function. |
In summary
- Do you need to modify data (
INSERT/UPDATE)? Use a Procedure. - Do you need to calculate something to display in a column or filter? Use a Function.
