User-Defined Functions in SQL Server

Three types of UDFs:

• Scalar function -> returns ONE value
• Inline Table-Valued Function (iTVF) -> returns a table from ONE SELECT (fastest, like a parameterized view)
• Multi-Statement TVF (mTVF) -> builds a @table with many statements (flexible, usually slower)

• Rules: functions CANNOT change data in tables (no INSERT/UPDATE/DELETE on real tables), cannot use TRY...CATCH, cannot call stored procedures, no NEWID()/RAND() inside.

Run the sample database script first. Every query below was run on that data, and the Output under each query is the real result.

USE SqlLearningDb;
GO

Topic table: TaxSlabs (Indian style income tax slabs - simplified, for practice only)

DROP TABLE IF EXISTS dbo.TaxSlabs;

CREATE TABLE dbo.TaxSlabs
(
    SlabID     INT PRIMARY KEY,
    FromAmount DECIMAL(12,2) NOT NULL,
    ToAmount   DECIMAL(12,2) NULL,         -- NULL = no upper limit
    RatePct    DECIMAL(5,2)  NOT NULL
);

INSERT INTO dbo.TaxSlabs (SlabID, FromAmount, ToAmount, RatePct) VALUES
(1,       0,  400000,  0),
(2,  400000,  800000,  5),
(3,  800000, 1200000, 10),
(4, 1200000, 1600000, 15),
(5, 1600000, 2000000, 20),
(6, 2000000, 2400000, 25),
(7, 2400000,    NULL, 30);
GO

(7 rows affected)

Part A: Scalar Functions

Simple scalar: full name with title

CREATE OR ALTER FUNCTION dbo.fn_FullName (@First VARCHAR(50), @Last VARCHAR(50), @Gender CHAR(1))
RETURNS VARCHAR(110)
WITH SCHEMABINDING            -- good practice: deterministic + protects referenced objects
AS
BEGIN
    RETURN CASE @Gender WHEN 'M' THEN 'Mr. ' WHEN 'F' THEN 'Ms. ' ELSE '' END
           + @First + ' ' + @Last;
END;
GO

Scalar with logic: years of service with label

CREATE OR ALTER FUNCTION dbo.fn_ServiceBand (@HireDate DATE, @AsOf DATE)
RETURNS VARCHAR(20)
AS
BEGIN
    DECLARE @Years INT = DATEDIFF(YEAR, @HireDate, @AsOf)
                         - CASE WHEN DATEADD(YEAR, DATEDIFF(YEAR, @HireDate, @AsOf), @HireDate) > @AsOf THEN 1 ELSE 0 END;
    RETURN CASE WHEN @Years >= 8 THEN 'Veteran (8+ yrs)'
                WHEN @Years >= 4 THEN 'Senior (4-7 yrs)'
                WHEN @Years >= 1 THEN 'Regular (1-3 yrs)'
                ELSE 'New Joiner' END;
END;
GO

Scalar reading a table: annual tax using slabs

CREATE OR ALTER FUNCTION dbo.fn_AnnualTax (@AnnualIncome DECIMAL(12,2))
RETURNS DECIMAL(12,2)
AS
BEGIN
    DECLARE @Tax DECIMAL(12,2);

    SELECT @Tax = SUM(
              (CASE WHEN @AnnualIncome < ISNULL(ToAmount, @AnnualIncome) THEN @AnnualIncome
                    ELSE ISNULL(ToAmount, @AnnualIncome) END - FromAmount) * RatePct / 100.0)
    FROM dbo.TaxSlabs
    WHERE @AnnualIncome > FromAmount;

    RETURN ISNULL(@Tax, 0);
END;
GO

Using scalar functions

SELECT dbo.fn_FullName(FirstName, LastName, Gender)  AS EmployeeName,
       HireDate,
       dbo.fn_ServiceBand(HireDate, '2025-06-30')     AS ServiceBand,
       Salary * 12                                    AS AnnualSalary,
       dbo.fn_AnnualTax(Salary * 12)                  AS AnnualTax
FROM dbo.Employees
ORDER BY AnnualSalary DESC;

Scalar function in a CHECK constraint / computed column is also possible

SELECT dbo.fn_AnnualTax(1000000) AS TaxOn10Lakh, dbo.fn_AnnualTax(3000000) AS TaxOn30Lakh;
GO

Part B: Inline Table-valued Functions (iTVF) -> preferred for performance

Orders between two dates for a customer (parameterized view)

CREATE OR ALTER FUNCTION dbo.fn_CustomerOrders (@CustomerID INT, @From DATE, @To DATE)
RETURNS TABLE
AS
RETURN
(
    SELECT o.OrderID, o.OrderDate, o.Status,
           SUM(oi.LineTotal) AS OrderTotal,
           COUNT(*)          AS Lines
    FROM dbo.Orders o
    JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
    WHERE o.CustomerID = @CustomerID
      AND o.OrderDate >= @From AND o.OrderDate < DATEADD(DAY, 1, @To)
    GROUP BY o.OrderID, o.OrderDate, o.Status
);
GO

SELECT * FROM dbo.fn_CustomerOrders(1, '2024-01-01', '2025-12-31') ORDER BY OrderDate;

-- iTVF can be joined, filtered and aggregated like a table
SELECT Status, COUNT(*) AS Orders, SUM(OrderTotal) AS Revenue
FROM dbo.fn_CustomerOrders(2, '2024-01-01', '2025-12-31')
GROUP BY Status;
GO

ITVF: product sales summary for a date range (used with CROSS APPLY in file 13 style)

CREATE OR ALTER FUNCTION dbo.fn_ProductSales (@ProductID INT, @From DATE, @To DATE)
RETURNS TABLE
AS
RETURN
(
    SELECT COUNT(*) AS Transactions, ISNULL(SUM(Quantity), 0) AS Units, ISNULL(SUM(TotalAmount), 0) AS Revenue
    FROM dbo.Sales
    WHERE ProductID = @ProductID AND SaleDate BETWEEN @From AND @To
);
GO

SELECT p.ProductName, s.Transactions, s.Units, s.Revenue
FROM dbo.Products p
CROSS APPLY dbo.fn_ProductSales(p.ProductID, '2025-01-01', '2025-03-31') s
ORDER BY s.Revenue DESC;
GO

ITVF: numbers table generator (very handy utility)

CREATE OR ALTER FUNCTION dbo.fn_Numbers (@N INT)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@N) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n
    FROM sys.all_objects a CROSS JOIN sys.all_objects b
);
GO

-- Generate a date list using the numbers function
SELECT DATEADD(DAY, n - 1, '2025-02-01') AS CalendarDate
FROM dbo.fn_Numbers(28);
GO

Part C: Multi-statement Table-valued Function (mTVF)

Org chart below a manager, with level (needs several steps -> mTVF)

CREATE OR ALTER FUNCTION dbo.fn_TeamOf (@ManagerID INT)
RETURNS @Team TABLE
(
    EmployeeID INT PRIMARY KEY,
    FullName   VARCHAR(110),
    JobTitle   VARCHAR(50),
    Lvl        INT,
    Salary     DECIMAL(10,2)
)
AS
BEGIN
    DECLARE @Level INT = 1;

    INSERT INTO @Team (EmployeeID, FullName, JobTitle, Lvl, Salary)
    SELECT EmployeeID, FirstName + ' ' + LastName, JobTitle, @Level, Salary
    FROM dbo.Employees WHERE ManagerID = @ManagerID;

    WHILE @@ROWCOUNT > 0
    BEGIN
        SET @Level += 1;
        INSERT INTO @Team (EmployeeID, FullName, JobTitle, Lvl, Salary)
        SELECT e.EmployeeID, e.FirstName + ' ' + e.LastName, e.JobTitle, @Level, e.Salary
        FROM dbo.Employees e
        JOIN @Team t ON t.EmployeeID = e.ManagerID AND t.Lvl = @Level - 1;
    END;

    RETURN;
END;
GO

SELECT * FROM dbo.fn_TeamOf(3) ORDER BY Lvl, EmployeeID;     -- everyone under the CTO
SELECT COUNT(*) AS TeamSize, SUM(Salary) AS TeamCost FROM dbo.fn_TeamOf(2);   -- under VP Sales
GO

MTVF: customer scorecard with derived fields computed step by step

CREATE OR ALTER FUNCTION dbo.fn_CustomerScorecard (@AsOf DATE)
RETURNS @Card TABLE
(
    CustomerID   INT PRIMARY KEY,
    CustomerName VARCHAR(100),
    Orders       INT,
    Revenue      DECIMAL(14,2),
    DaysSinceLast INT,
    Segment      VARCHAR(20)
)
AS
BEGIN
    INSERT INTO @Card (CustomerID, CustomerName, Orders, Revenue, DaysSinceLast)
    SELECT c.CustomerID, c.CustomerName,
           COUNT(DISTINCT o.OrderID),
           ISNULL(SUM(oi.LineTotal), 0),
           DATEDIFF(DAY, MAX(o.OrderDate), @AsOf)
    FROM dbo.Customers c
    LEFT JOIN dbo.Orders o      ON o.CustomerID = c.CustomerID AND o.Status <> 'Cancelled'
    LEFT JOIN dbo.OrderItems oi ON oi.OrderID = o.OrderID
    GROUP BY c.CustomerID, c.CustomerName;

    UPDATE @Card
    SET Segment = CASE WHEN Orders = 0                                THEN 'Never Bought'
                       WHEN DaysSinceLast <= 90  AND Revenue >= 500000 THEN 'Champion'
                       WHEN DaysSinceLast <= 180                        THEN 'Active'
                       WHEN DaysSinceLast <= 365                        THEN 'At Risk'
                       ELSE 'Lost' END;
    RETURN;
END;
GO

SELECT * FROM dbo.fn_CustomerScorecard('2025-07-01') ORDER BY Revenue DESC;
SELECT Segment, COUNT(*) AS Customers FROM dbo.fn_CustomerScorecard('2025-07-01') GROUP BY Segment;
GO

Part D: Performance Notes & Metadata

Scalar UDF inlining (SQL Server 2019+): check if a function can be inlined

SELECT OBJECT_NAME(object_id) AS FunctionName, is_inlineable
FROM sys.sql_modules
WHERE OBJECT_NAME(object_id) LIKE 'fn[_]%';

List all functions in the database with their type

SELECT name, type_desc, create_date
FROM sys.objects
WHERE type IN ('FN', 'IF', 'TF')    -- FN = scalar, IF = inline TVF, TF = multi-statement TVF
ORDER BY type_desc, name;

FUNCTION vs STORED PROCEDURE
| Function                          | Stored Procedure                  |
|-----------------------------------|-----------------------------------|
| Must return a value / table       | Return is optional (int only)     |
| Usable in SELECT, WHERE, JOIN     | Called with EXEC only             |
| Cannot modify table data          | Can INSERT/UPDATE/DELETE          |
| No TRY...CATCH, no transactions   | Supports both                     |

PRACTICE QUESTIONS 1. Write a scalar function fn_GetDiscountedPrice(@Price, @DiscountPct) and use it on Products. 2. Write an iTVF fn_EmployeesByCity(@City) and join it with Departments. 3. Write fn_CategoryPath(@CategoryID) that returns 'Electronics > Computers > Laptops' (scalar, using a loop). 4. Write an mTVF that returns monthly revenue for a given year with a "Growth %" column. 5. Compare the execution time of a scalar UDF vs the same logic written inline in a SELECT.