The Code Neuron

Roadmaps
Tech Stack
Get In Touch

User-Defined Functions (UDFs): Extending SQL’s Capabilities

—

by

thecodeneuron
in ,

Welcome back to our journey through MSSQL! In our last post, Stored Procedures and Rules: Enhancing Database Control , we mastered encapsulating complex logic with Stored Procedures and understood why CHECK Constraints are vital for data integrity. Now, let’s explore User-Defined Functions (UDFs), another powerful way to extend SQL’s functionality and make your code more modular and reusable.

UDFs are like custom-built functions you define yourself, performing specific actions and returning a value. Unlike stored procedures, UDFs can be used directly within SQL queries (e.g., in SELECT, WHERE, or HAVING clauses), which provides incredible flexibility for data manipulation and reporting. For freshers, think of a UDF as a mini-program that takes inputs, does a job, and gives you a single result or a table of results back, all within your SQL query.

For consistency, we will continue using our familiar eCommerce database with TeamMembers, ClientAccounts, and Transactions tables for our examples. we will also introduce a PurchasedItems table for some example.

First, let’s ensure we have some base data in our Transactions and ClientAccounts tables, and that TeamMembers has commission_rate.

SQL
-- Create TeamMembers  table if not created already
CREATE TABLE TeamMembers (
    member_id INT NOT NULL,
    member_name VARCHAR(50) NOT NULL,
    location_city VARCHAR(50) NOT NULL,
    commission_rate DECIMAL(4,2) NOT NULL,
    PRIMARY KEY (member_id)
);

-- Sample data if not already populated from previous blogs
-- INSERT INTO TeamMembers (member_id, member_name, location_city, commission_rate) VALUES
-- (101, 'Alice Johnson', 'New York', 0.10),
-- (102, 'Bob Williams', 'London', 0.08);

-- Create ClientAccounts table if not created already
CREATE TABLE ClientAccounts (
    client_id INT NOT NULL,
    client_name VARCHAR(50) NOT NULL,
    client_city VARCHAR(50) NOT NULL,
    satisfaction_rating INT NOT NULL,
    assigned_member_id INT NOT NULL,
    PRIMARY KEY (client_id),
    FOREIGN KEY (assigned_member_id) REFERENCES TeamMembers(member_id)
);


-- INSERT INTO ClientAccounts (client_id, client_name, client_city, satisfaction_rating, assigned_member_id) VALUES
-- (2001, 'Global Corp', 'London', 250, 101),
-- (2002, 'Tech Solutions Inc.', 'San Jose', 280, 102);

-- Create Transactions table if not created already
CREATE TABLE Transactions (
    transaction_id INT NOT NULL PRIMARY KEY,
    amt DECIMAL(7,2) NOT NULL,
    odate DATE NOT NULL,
    cnum INT NOT NULL,
    FOREIGN KEY (cnum) REFERENCES ClientAccounts(client_id)
);

-- INSERT INTO Transactions (transaction_id, amt, odate, cnum) VALUES
-- (3001, 1500.00, '2025-07-01', 2001),
-- (3002, 250.00, '2025-07-01', 2002),
-- (3003, 800.00, '2025-07-02', 2001),
-- (3004, 500.00, '2025-07-03', 2002);

Understanding User-Defined Functions (UDFs)

A User-Defined Function (UDF) in SQL Server is a routine that accepts parameters, executes a set of SQL statements, and returns a result.

There are primarily two types of UDFs we’ll focus on:

  1. Scalar Functions: These functions return a single data value (e.g., a number, a string, a date). They are perfect for calculations, data formatting, or simple lookups.
  2. Table-Valued Functions (TVFs): These functions return a result set in the form of a table. They come in two sub-types:
    • Inline Table-Valued Functions (ITVFs): These are the most efficient TVFs, acting like parameterized views with a single SELECT statement.
    • Multi-Statement Table-Valued Functions (MSTVFs): These allow for more complex logic and multiple SQL statements within their body to build the result table.

Benefits of Using UDFs

  • Modularity & Reusability: Write complex logic once and use it across multiple queries.
  • Improved Readability: Break down complex queries into smaller, more understandable components.
  • Enhanced Maintainability: Changes to logic are made in one central UDF definition, affecting all places it’s used.
  • Reduced Network Traffic: Operations are performed on the server, sending only the final result to the client.

Scalar Functions: Returning a Single Value

Scalar functions are ideal for calculating a single value. Let’s create an example that calculates a “commission earned” for a transaction, applying the team member’s commission rate.

Now, let’s create our scalar function:

SQL
--function to calculate commission earned for transaction
CREATE FUNCTION udfCalculateTransactionCommission (
    @transaction_amount DECIMAL(7,2), -- Matches amt column type
    @commission_rate DECIMAL(4,2)     -- Matches commission_rate column type
)
RETURNS DECIMAL(7,2) -- Return type should accommodate the result
AS
BEGIN
    -- Calculate commission based on transaction amount and commission rate
    RETURN @transaction_amount * @commission_rate;
END;
GO

After the execution –

How it works: This scalar function takes a transaction_amount (from amt) and a commission_rate as inputs and returns the calculated commission.

Calling a Scalar Function:

You can call scalar functions independently or, more commonly, within SELECT statements alongside your table data.

  • Calling Independently:
SQL
-- Calling the function independently
SELECT dbo.udfCalculateTransactionCommission(1000.00, 0.10) AS ExampleCommission;
GO

Output

  • Calling with Table Data:
SQL
-- Calling the function with our eCommerce tables
SELECT
    t.transaction_id,
    t.amt AS TransactionAmount,
    tm.member_name,
    tm.commission_rate,
    dbo.udfCalculateTransactionCommission(t.amt, tm.commission_rate) AS EarnedCommission
FROM
    Transactions t
JOIN
    ClientAccounts ca ON t.cnum = ca.client_id
JOIN
    TeamMembers tm ON ca.assigned_member_id = tm.member_id
WHERE
    t.odate >= '2025-07-01';
GO

Output:

This makes it easy to add calculated fields to your reports without repeating complex logic.

Table-Valued Functions (TVFs): Returning a Table

TVFs are incredibly powerful as they return an entire result set, which can then be queried further, just like a regular table.

Inline Table-Valued Functions (ITVFs)

ITVFs are generally the most performant TVFs because they are essentially parameterized views. SQL Server can optimize them very effectively.

Let’s create an ITVF to find all ClientAccounts assigned to a specific TeamMember who have a satisfaction_rating above a certain threshold.

SQL
CREATE FUNCTION dbo.GetHighSatisfactionClientsByTeamMember (
    @teamMemberId INT,
    @minSatisfactionRating INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        ca.client_id,
        ca.client_name,
        ca.client_city,
        ca.satisfaction_rating,
        tm.member_name AS AssignedTeamMemberName -- Added for clarity in output
    FROM
        ClientAccounts ca
    JOIN
        TeamMembers tm ON ca.assigned_member_id = tm.member_id
    WHERE
        tm.member_id = @teamMemberId
        AND ca.satisfaction_rating >= @minSatisfactionRating
);
GO

How it works: This ITVF takes a teamMemberId (matching TeamMembers.member_id) and a minSatisfactionRating (matching ClientAccounts.satisfaction_rating). It returns a table containing details of clients who meet these criteria.

Execution: Since ITVFs return a table, you can query them just like you would any other table.

SQL
-- Get clients with satisfaction rating >= 200 assigned to Team Member 101
SELECT * FROM dbo.GetHighSatisfactionClientsByTeamMember(101, 200);
GO

-- Get only the client names from the result
SELECT client_name FROM dbo.GetHighSatisfactionClientsByTeamMember(101, 250);
GO

-- Count the number of high-satisfaction clients for a team member
SELECT COUNT(client_id) FROM dbo.GetHighSatisfactionClientsByTeamMember(101, 150);
GO

-- Calculate the average satisfaction rating for these clients
SELECT AVG(satisfaction_rating) FROM dbo.GetHighSatisfactionClientsByTeamMember(101, 200);
GO

Output: If executed above queries simultaneously, we will be getting below output.

You can even join ITVFs with other tables or views, making them highly versatile for complex reporting.

Multi-Statement Table-Valued Functions (MSTVFs)

MSTVFs allow for more complex logic, including multiple SQL statements, before populating and returning a table variable. They are less performant than ITVFs but offer more flexibility.

To demonstrate this, we’ll introduce a new table to our eCommerce database: PurchasedItems. This table will store details about individual products within an order, distinct from the Transactions table which might represent the overall order total.

SQL
-- New permanent table for eCommerce database for this series
-- This table will store individual items purchased within an order
CREATE TABLE PurchasedItems (
    item_id INT IDENTITY(1,1) PRIMARY KEY, -- Unique identifier for each purchased item
    order_id INT NOT NULL,               -- Reference to a logical order (can be linked to Transactions.transaction_id if needed)
    prod_id VARCHAR(10) NOT NULL,        -- Product identifier
    quantity INT NOT NULL,
    list_price DECIMAL(10,2) NOT NULL,
    discount DECIMAL(4,2) NOT NULL       -- Discount as a percentage (e.g., 2.5 for 2.5%)
);

-- Sample data for PurchasedItems
INSERT INTO PurchasedItems (order_id, prod_id, quantity, list_price, discount) VALUES
(1, 'P001', 100, 50.00, 2.5),
(2, 'P002', 50, 40.00, 4.0),
(3, 'P004', 1000, 25.00, 0.0),
(1, 'P005', 200, 10.00, 1.0), -- Another item for order 1
(4, 'P001', 200, 50.00, 2.5);
GO

Now, let’s create an MSTVF that calculates the net sale for purchased items above a certain quantity threshold.

SQL
--MSTVF that calculates the net sale for purchased items above a certain quantity threshold.
CREATE FUNCTION dbo.udfGetNetSalesForHighVolumePurchases (@minQuantity INT)
RETURNS @NetSales TABLE
(
    ItemID INT,
    OrderID INT,
    ProductID VARCHAR(10),
    Quantity INT,
    NetSaleAmount DECIMAL(10,2)
)
AS
BEGIN
    INSERT INTO @NetSales (ItemID, OrderID, ProductID, Quantity, NetSaleAmount)
    SELECT
        item_id,
        order_id,
        prod_id,
        quantity,
        quantity * list_price * ((100 - discount) / 100) AS NetSale -- Calculation for net sale
    FROM
        PurchasedItems
    WHERE
        quantity >= @minQuantity;

    -- You could add more complex logic here within the function,
    -- for example, applying additional tiers of discounts based on total quantity,
    -- or logging the items found.
    -- UPDATE @NetSales SET NetSaleAmount = NetSaleAmount * 0.9 WHERE Quantity > 500;

    RETURN;
END;
GO

How it works: This MSTVF populates a table variable (@NetSales) with calculated net sales for items meeting a minQuantity threshold from our new PurchasedItems table. It demonstrates how multiple steps or internal calculations could occur before the final result is returned.

Execution:

SQL
-- Get net sales for items with quantity 100 or more
SELECT * FROM dbo.udfGetNetSalesForHighVolumePurchases(100);
GO

-- Get net sales for items with quantity 500 or more
SELECT * FROM dbo.udfGetNetSalesForHighVolumePurchases(500);
GO

Output:

Functions vs. Stored Procedures vs. Triggers: A Quick Comparison

It’s important to differentiate UDFs from their counterparts, Stored Procedures and Triggers, to choose the right tool for the job.

FeatureUser-Defined Functions (UDFs)Stored ProceduresTriggers
PurposeComputations, data transformation, return single value/table result for query.Perform sequence of actions, data modification, return result sets/output parameters.Automatically execute in response to DML events (INSERT, UPDATE, DELETE).
Return ValueAlways returns a value (scalar or table).Can return 0 or more result sets, output parameters, and a return status.Do not return values directly to the caller.
Usage in QueriesCan be used directly within SELECT, WHERE, HAVING, FROM clauses.Must be EXEC uted; cannot be used directly in SQL statements.Implicitly executed by DML operations; cannot be called directly.
Data ModificationGenerally cannot perform DML (INSERT, UPDATE, DELETE) or DDL (CREATE, ALTER, DROP) statements (except MSTVFs populating their table variable, but not directly on base tables).Can perform DML and DDL statements.Designed to perform DML/DDL based on database events.
Transaction ManagementCannot manage transactions (BEGIN TRAN, COMMIT, ROLLBACK).Can manage transactions, suitable for complex data modifications.Part of the triggering transaction; can implicitly cause rollbacks.
PerformanceITVFs are highly optimized. Scalar and MSTVFs can sometimes face performance challenges.Generally good for complex operations involving data modification.Can impact DML performance if not optimized.
Error HandlingLimited; TRY...CATCH is not directly supported within UDFs.Full TRY...CATCH support for robust error handling.Can include TRY...CATCH for handling errors during their execution.

Key Takeaway:

  • Use UDFs for calculations, data transformations, or to return a result set that can be consumed within another query.
  • Use Stored Procedures for complex sequences of actions, data modifications, transaction management, or when you need to return multiple distinct result sets.
  • Use Triggers for automated actions that need to occur in response to specific data modification events on a table.

Dropping User-Defined Functions and Tables

To remove a UDF, you use the DROP FUNCTION statement, specifying the function name:

SQL
DROP FUNCTION dbo.udfCalculateTransactionCommission;
DROP FUNCTION dbo.GetHighSatisfactionClientsByTeamMember;
DROP FUNCTION dbo.udfGetNetSalesForHighVolumePurchases;
GO

-- To drop the new permanent table 'PurchasedItems' (use with caution in a real environment)
-- DROP TABLE PurchasedItems;
-- GO

Conclusion

User-Defined Functions are an essential tool in your MSSQL arsenal, enabling you to write more modular, readable, and reusable SQL code. By encapsulating logic into scalar or table-valued functions, you empower your queries with custom computations and flexible data retrieval. Understanding their strengths and limitations, especially in contrast to stored procedures and triggers, is key to designing efficient and maintainable database solutions.

Our next blog post will delve into two crucial concepts that help in writing efficient and logical SQL queries: Subqueries and Candidate Key. Subqueries allow you to create powerful nested queries, while understanding Candidate Keys is fundamental to designing robust and relational database schemas.

Stay tuned for more!

Hi there 👋
It’s nice to see you.

Sign up to receive awesome content in your inbox, as soon as they gets posted!

This field is required.

We don’t spam! Read our privacy policy for more info.

Comments

Leave a Reply