Day 18: User-defined functions

A. Lesson overview

Activity breakdown

Business scenario. The same calculations — line net value, price band, working days between two dates, “is this a valid email?” — are copied into dozens of queries, with small differences. The team wants a shared library of functions in the Lab schema, without making reports slow.

B. Theory explained clearly

Part 1 — Three kinds of function

Calling:

SELECT Lab.fn_LineNet(2, 189.00, 0.05);            -- scalar: always schema-qualified, with parentheses
SELECT * FROM Lab.fn_OrdersBetween('2025-12-01', '2026-01-01');   -- TVF: in FROM

Functions must not have side effects. Inside a function you cannot:

• INSERT/UPDATE/DELETE a permanent or temporary table (an MSTVF may modify its own return table variable),
• execute dynamic SQL or (non-extended) stored procedures,
• use TRY…CATCH, THROW or RAISERROR,
• call side-effecting or random built-ins such as NEWID() or RAND(),
• change session settings or use transactions.

That restriction is what lets SQL Server call a function as part of a query, as many times as it needs, in any order.

Part 2 — Scalar functions

CREATE OR ALTER FUNCTION Lab.fn_LineNet
(
    @Quantity    int,
    @UnitPrice   decimal(10,2),
    @DiscountPct decimal(4,2)
)
RETURNS decimal(19,4)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN @Quantity * @UnitPrice * (1 - @DiscountPct);
END;
GO

Determinism. A function is deterministic if it always returns the same result for the same input. Only deterministic, precise, schema-bound functions can be used in persisted computed columns and indexes. Check with SELECT OBJECTPROPERTY(OBJECT_ID('Lab.fn_LineNet'), 'IsDeterministic');.

The performance story: scalar UDF inlining

Before SQL Server 2019, a scalar function was executed once per row, as a separate call that the optimiser could not see into, and it prevented parallel plans. On large tables this was a classic cause of slow queries.

SQL Server 2019 and later (database compatibility level 150+) can inline many scalar functions: the function’s logic is rewritten into the calling query, as if you had typed the expression yourself. A function is inlineable only if it meets a list of conditions — for example, it must not use time-dependent functions such as GETDATE(), table variables, EXECUTE AS, or be referenced in a computed column or CHECK constraint, among others. You can check:

SELECT OBJECT_NAME(object_id) AS FunctionName, is_inlineable
FROM   sys.sql_modules
WHERE  object_id = OBJECT_ID('Lab.fn_LineNet');

is_inlineable = 1 means the function can be inlined; whether it is in a particular query can be seen in the execution plan (Day 22). Functions that read tables (like a working-days lookup) can often be inlined too, but complex ones may not be. When a scalar function is not inlined, prefer an inline TVF and APPLY for heavy use.

Part 3 — Inline vs multi-statement TVFs

-- Inline: a single SELECT
CREATE OR ALTER FUNCTION Lab.fn_OrdersBetween (@From date, @To date)
RETURNS TABLE
AS
RETURN
(
    SELECT o.OrderID, o.CustomerID, o.OrderDate, SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS OrderNet
    FROM   Sales.SalesOrder AS o JOIN Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE  o.OrderStatus <> 'Cancelled' AND o.OrderDate >= @From AND o.OrderDate < @To
    GROUP BY o.OrderID, o.CustomerID, o.OrderDate
);
GO

-- Multi-statement: fills a table variable
CREATE OR ALTER FUNCTION Lab.fn_OrdersBetween_MS (@From date, @To date)
RETURNS @Result TABLE (OrderID int PRIMARY KEY, CustomerID int, OrderDate date, OrderNet decimal(19,4))
AS
BEGIN
    INSERT INTO @Result (OrderID, CustomerID, OrderDate, OrderNet)
    SELECT … same query … ;
    RETURN;
END;
GO

On today’s small tables both are instant; Day 22’s larger data set is where the difference becomes visible. The rule of thumb stands regardless: prefer inline.

Part 4 — Function, view or procedure?

C. Dataset and starting state

Preparation. Lab.Calendar must exist — if SELECT COUNT(*) FROM Lab.Calendar; fails, run scripts/lessons/day12_calendar_and_temporal.sql.

Validation. Expected: 1096, 30, 170.

USE RetailLab;
GO
SELECT  (SELECT COUNT(*) FROM Lab.Calendar)       AS CalendarDays,
        (SELECT COUNT(*) FROM Inventory.Product)  AS Products,
        (SELECT compatibility_level FROM sys.databases WHERE name = DB_NAME()) AS CompatLevel;

D. Worked examples

Worked example 1 — A scalar function and its properties

CREATE OR ALTER FUNCTION Lab.fn_LineNet
(
    @Quantity    int,
    @UnitPrice   decimal(10,2),
    @DiscountPct decimal(4,2)
)
RETURNS decimal(19,4)
WITH SCHEMABINDING, RETURNS NULL ON NULL INPUT
AS
BEGIN
    RETURN @Quantity * @UnitPrice * (1 - @DiscountPct);
END;
GO

SELECT  Lab.fn_LineNet(2, 189.00, 0.05)                           AS OneLine,          -- 359.1000
        Lab.fn_LineNet(NULL, 189.00, 0.05)                        AS NullInput,        -- NULL
        OBJECTPROPERTY(OBJECT_ID('Lab.fn_LineNet'), 'IsDeterministic') AS IsDeterministic,
        (SELECT is_inlineable FROM sys.sql_modules WHERE object_id = OBJECT_ID('Lab.fn_LineNet')) AS IsInlineable;

SELECT CAST(SUM(Lab.fn_LineNet(Quantity, UnitPrice, DiscountPct)) AS decimal(12,2)) AS AllLines
FROM   Sales.SalesOrderLine;

Expected result: 359.1000, NULL, 1, 1; then 124384.43 (the validation checksum 124384.4345, rounded). Remove WITH SCHEMABINDING and re-create: IsDeterministic becomes 0.

Worked example 2 — Inline and multi-statement versions of the same TVF

CREATE OR ALTER FUNCTION Lab.fn_OrdersBetween (@From date, @To date)
RETURNS TABLE
AS
RETURN
(
    SELECT  o.OrderID, o.CustomerID, o.OrderDate,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS OrderNet
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= @From AND o.OrderDate < @To
    GROUP BY o.OrderID, o.CustomerID, o.OrderDate
);
GO

CREATE OR ALTER FUNCTION Lab.fn_OrdersBetween_MS (@From date, @To date)
RETURNS @Result TABLE
(
    OrderID    int           NOT NULL PRIMARY KEY,
    CustomerID int           NOT NULL,
    OrderDate  date          NOT NULL,
    OrderNet   decimal(19,4) NOT NULL
)
AS
BEGIN
    INSERT INTO @Result (OrderID, CustomerID, OrderDate, OrderNet)
    SELECT  o.OrderID, o.CustomerID, o.OrderDate,
            SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct))
    FROM    Sales.SalesOrder     AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.OrderStatus <> 'Cancelled'
      AND   o.OrderDate >= @From AND o.OrderDate < @To
    GROUP BY o.OrderID, o.CustomerID, o.OrderDate;
    RETURN;
END;
GO

SELECT 'Inline' AS Version, COUNT(*) AS Orders, CAST(SUM(OrderNet) AS decimal(12,2)) AS NetSales
FROM   Lab.fn_OrdersBetween('2025-01-01', '2026-01-01')
UNION ALL
SELECT 'Multi-statement', COUNT(*), CAST(SUM(OrderNet) AS decimal(12,2))
FROM   Lab.fn_OrdersBetween_MS('2025-01-01', '2026-01-01');

Expected result: both rows show 198 and 63741.94. Same answer — the difference is how SQL Server gets there. Turn on Include Actual Execution Plan (Ctrl + M) and run each SELECT separately: the inline version shows the underlying tables directly; the multi-statement version shows a Table Valued Function operator feeding a table scan of its result. Day 22 explains how to read these plans.

Worked example 3 — A scalar function that reads a table

CREATE OR ALTER FUNCTION Lab.fn_WorkingDaysBetween (@Start date, @End date)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    RETURN (SELECT COUNT(*)
            FROM   Lab.Calendar
            WHERE  CalendarDate >  @Start
              AND  CalendarDate <= @End
              AND  IsWorkingDay = 1);
END;
GO

SELECT Lab.fn_WorkingDaysBetween('2025-12-19', '2025-12-29') AS WorkingDays,   -- 4
       (SELECT is_inlineable FROM sys.sql_modules
        WHERE object_id = OBJECT_ID('Lab.fn_WorkingDaysBetween')) AS IsInlineable;

Expected result: 4 (22, 23, 24 and 29 December; the 25th and 26th are bank holidays; the 20th, 21st, 27th and 28th are weekends) and IsInlineable should be 1 (a single SELECT with an aggregate is within the inlining rules). Because of SCHEMABINDING, DROP TABLE Lab.Calendar now fails until the function is dropped — protection against silent breakage. This also means the Day 12 script cannot be re-run while this function exists; section I gives the cleanup order.

Worked example 4 — Scalar function or APPLY?

CREATE OR ALTER FUNCTION Lab.tvf_WorkingDaysBetween (@Start date, @End date)
RETURNS TABLE
WITH SCHEMABINDING
AS
RETURN (SELECT COUNT(*) AS WorkingDays
        FROM   Lab.Calendar
        WHERE  CalendarDate > @Start AND CalendarDate <= @End AND IsWorkingDay = 1);
GO

SELECT  COUNT(*)                                                              AS ShippedOrders,
        SUM(CASE WHEN Lab.fn_WorkingDaysBetween(o.OrderDate, o.ShippedDate) <= 2 THEN 1 ELSE 0 END) AS WithinSLA_Scalar,
        SUM(CASE WHEN w.WorkingDays <= 2 THEN 1 ELSE 0 END)                    AS WithinSLA_TVF
FROM    Sales.SalesOrder AS o
CROSS APPLY Lab.tvf_WorkingDaysBetween(o.OrderDate, o.ShippedDate) AS w
WHERE   o.SalesChannel = 'Online' AND o.ShippedDate IS NOT NULL
  AND   o.OrderDate >= '2025-01-01' AND o.OrderDate < '2026-01-01';

Expected result: 106, 88, 88 — Day 12’s SLA result, now from reusable functions. Both approaches agree; the inline TVF is guaranteed to be optimised as part of the query, while the scalar version relies on inlining.

E. Guided practical lab — A small function library with tests

Build three functions and a test query that proves they work, including edge cases.

Step 1 — Price band (scalar, deterministic)

1. Do: Create the function.
2. Run:

   USE RetailLab;
   GO
   
   CREATE OR ALTER FUNCTION Lab.fn_PriceBand (@ListPrice decimal(10,2))
   RETURNS varchar(10)
   WITH SCHEMABINDING
   AS
   BEGIN
       RETURN CASE
                  WHEN @ListPrice IS NULL THEN NULL
                  WHEN @ListPrice < 50    THEN 'Budget'
                  WHEN @ListPrice < 150   THEN 'Mid-range'
                  ELSE                         'Premium'
              END;
   END;
   GO

3. Observe: Commands completed successfully.
4. Why it matters: Day 1’s CASE, Day 8’s lookup table and now a function — three ways to encode one rule. A function guarantees every report uses the same boundaries.
5. Confirm: SELECT OBJECTPROPERTY(OBJECT_ID('Lab.fn_PriceBand'), 'IsDeterministic'); → 1.

Step 2 — Test with edge cases

1. Do: Test boundaries and NULL in one query.
2. Run:

   SELECT  v.TestValue, Lab.fn_PriceBand(v.TestValue) AS Band, v.Expected,
           CASE WHEN Lab.fn_PriceBand(v.TestValue) = v.Expected
                  OR (Lab.fn_PriceBand(v.TestValue) IS NULL AND v.Expected IS NULL)
                THEN 'PASS' ELSE 'FAIL' END AS Result
   FROM   (VALUES (CAST(49.99 AS decimal(10,2)), 'Budget'),
                  (50.00,  'Mid-range'),
                  (149.99, 'Mid-range'),
                  (150.00, 'Premium'),
                  (NULL,   NULL)) AS v (TestValue, Expected);

3. Observe: 5 rows, all PASS.
4. Why it matters: Boundaries are where business rules break. A tiny test table makes the rule explicit and repeatable.
5. Confirm: no FAIL.

Step 3 — Use it on real data

1. Do: Count products per band.
2. Run:

   SELECT Lab.fn_PriceBand(ListPrice) AS Band, COUNT(*) AS Products
   FROM   Inventory.Product
   GROUP BY Lab.fn_PriceBand(ListPrice)
   ORDER BY MIN(ListPrice);

3. Observe: Budget 8, Mid-range 10, Premium 12 — the same as Day 1.
4. Why it matters: A function call can be grouped on like any expression.
5. Confirm: 30 products in total.

Step 4 — An inline TVF for “top N products in a year”

1. Do: Create a parameterised top-N function and apply it to several years at once.

2. Run:

   CREATE OR ALTER FUNCTION Lab.fn_TopProducts (@Year int, @N int)
   RETURNS TABLE
   AS
   RETURN
   (
       SELECT TOP (@N)
               p.ProductCode, p.ProductName,
               SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)) AS NetSales
       FROM    Sales.SalesOrder     AS o
       JOIN    Sales.SalesOrderLine AS l ON l.OrderID   = o.OrderID
       JOIN    Inventory.Product    AS p ON p.ProductID = l.ProductID
       WHERE   o.OrderStatus <> 'Cancelled'
         AND   o.OrderDate >= DATEFROMPARTS(@Year, 1, 1) AND o.OrderDate < DATEFROMPARTS(@Year + 1, 1, 1)
       GROUP BY p.ProductCode, p.ProductName
       ORDER BY NetSales DESC, p.ProductCode
   );
   GO
   
   SELECT  y.Yr, t.ProductCode, t.ProductName, CAST(t.NetSales AS decimal(12,2)) AS NetSales
   FROM   (VALUES (2024), (2025)) AS y (Yr)
   CROSS APPLY Lab.fn_TopProducts(y.Yr, 3) AS t
   ORDER BY y.Yr, t.NetSales DESC;

3. Observe:

   Yr	ProductCode	ProductName	NetSales
   2024	TNT-002	Summit 4-Person Tent	7119.60
   2024	TNT-004	Classic Canvas Bell Tent	6768.70
   2024	NAV-002	Handheld GPS Unit	5516.55
   2025	TNT-002	Summit 4-Person Tent	8777.35
   2025	TNT-003	Ultralight Solo Shelter	5241.45
   2025	SLP-002	Arctic Down Sleeping Bag	4903.60

4. Why it matters: An inline TVF + CROSS APPLY over a list of parameter values replaces a loop of procedure calls. The bell tent was 2024’s number two product — and discontinued at year end.

5. Confirm: 6 rows.

F. Independent practice

Task 1 — Margin percentage function

Create Lab.fn_MarginPct(@ListPrice decimal(10,2), @StandardCost decimal(10,2)) RETURNS decimal(5,1) — schema-bound, returning NULL when the price is 0 or NULL. Use it to list the three highest-margin products.

Success criteria: NAV-003 66.6, NAV-001 64.0, BSL-003 62.5. IsDeterministic = 1.

Task 2 — Persisted computed column

Copy Inventory.Product to Lab.Product, then add MarginPct AS Lab.fn_MarginPct(ListPrice, StandardCost) PERSISTED. Query it for TNT-004 and NAV-002. Then try to ALTER the function to remove SCHEMABINDING and observe what happens.

Success criteria: TNT-004 46.6, NAV-002 36.5. Altering the function fails because a persisted computed column depends on it (Cannot ALTER ‘Lab.fn_MarginPct’ because it is being referenced by object ‘Product’.).

Task 3 — Customer timeline: multi-statement, then inline

Write Lab.fn_CustomerTimeline_MS(@CustomerID int) returning EventDate, EventType (Order / Payment), OrderID and Amount — orders at amount due (net rounded to pence plus shipping), and payments. Then write the inline equivalent Lab.fn_CustomerTimeline using UNION ALL. Compare them for customer 35 with EXCEPT in both directions.

Success criteria: customer 35: 4 rows — order and payment for 1362 (33.95, 21 Dec 2025) and for 1364 (476.00, 22 Dec 2025). Both EXCEPTs return 0 rows.

Task 4 — A function that is not inlineable (challenging)

Create Lab.fn_DaysSinceOrderToday(@OrderDate date) RETURNS int that uses GETDATE(). Check is_inlineable. Then create an equivalent that takes the reference date as a parameter, Lab.fn_DaysBetween(@From date, @To date), and check again. Explain the difference in one sentence.

Success criteria: the GETDATE() version reports is_inlineable = 0; the parameterised version reports 1.

Stretch challenge — A validation function in a CHECK constraint

Create Lab.fn_IsValidEmail(@Email nvarchar(255)) RETURNS bit using REGEXP_LIKE. Create Lab.ContactSignup (SignupID int IDENTITY PRIMARY KEY, Email nvarchar(255) NOT NULL, CONSTRAINT CK_ContactSignup_Email CHECK (Lab.fn_IsValidEmail(Email) = 1)). Insert the 15 import emails one at a time in a loop or individually, counting successes and failures.

Success criteria: 12 rows accepted (the 14 non-NULL emails minus 2 invalid); 2 inserts fail with a CHECK constraint violation; the NULL email fails the NOT NULL rule. Note that a function used in a CHECK constraint is never inlined there, even if is_inlineable shows 1.

G. Solutions and explanations

CREATE OR ALTER FUNCTION Lab.fn_MarginPct (@ListPrice decimal(10,2), @StandardCost decimal(10,2))
RETURNS decimal(5,1)
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE WHEN @ListPrice IS NULL OR @ListPrice = 0 OR @StandardCost IS NULL THEN NULL
                ELSE CAST(100.0 * (@ListPrice - @StandardCost) / @ListPrice AS decimal(5,1)) END;
END;
GO

SELECT TOP (3) ProductCode, Lab.fn_MarginPct(ListPrice, StandardCost) AS MarginPct
FROM   Inventory.Product
ORDER BY MarginPct DESC, ProductCode;

DROP TABLE IF EXISTS Lab.Product;
SELECT * INTO Lab.Product FROM Inventory.Product;
ALTER TABLE Lab.Product ADD CONSTRAINT PK_LabProduct PRIMARY KEY (ProductID);

ALTER TABLE Lab.Product ADD MarginPct AS Lab.fn_MarginPct(ListPrice, StandardCost) PERSISTED;

SELECT ProductCode, MarginPct FROM Lab.Product WHERE ProductCode IN ('TNT-004', 'NAV-002') ORDER BY ProductCode;

GO

-- Try to change the function
CREATE OR ALTER FUNCTION Lab.fn_MarginPct (@ListPrice decimal(10,2), @StandardCost decimal(10,2))
RETURNS decimal(5,1)
AS
BEGIN
    RETURN NULL;
END;
GO

CREATE OR ALTER FUNCTION Lab.fn_CustomerTimeline_MS (@CustomerID int)
RETURNS @T TABLE (EventDate date, EventType varchar(10), OrderID int, Amount decimal(12,2))
AS
BEGIN
    INSERT INTO @T (EventDate, EventType, OrderID, Amount)
    SELECT  o.OrderDate, 'Order', o.OrderID,
            ROUND(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)), 2) + o.ShippingCost
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.CustomerID = @CustomerID
    GROUP BY o.OrderDate, o.OrderID, o.ShippingCost;

    INSERT INTO @T (EventDate, EventType, OrderID, Amount)
    SELECT  p.PaymentDate, 'Payment', p.OrderID, p.Amount
    FROM    Sales.Payment AS p
    JOIN    Sales.SalesOrder AS o ON o.OrderID = p.OrderID
    WHERE   o.CustomerID = @CustomerID;

    RETURN;
END;
GO

CREATE OR ALTER FUNCTION Lab.fn_CustomerTimeline (@CustomerID int)
RETURNS TABLE
AS
RETURN
(
    SELECT  o.OrderDate AS EventDate, CAST('Order' AS varchar(10)) AS EventType, o.OrderID,
            CAST(ROUND(SUM(l.Quantity * l.UnitPrice * (1 - l.DiscountPct)), 2) + o.ShippingCost AS decimal(12,2)) AS Amount
    FROM    Sales.SalesOrder AS o
    JOIN    Sales.SalesOrderLine AS l ON l.OrderID = o.OrderID
    WHERE   o.CustomerID = @CustomerID
    GROUP BY o.OrderDate, o.OrderID, o.ShippingCost
    UNION ALL
    SELECT  p.PaymentDate, 'Payment', p.OrderID, p.Amount
    FROM    Sales.Payment AS p
    JOIN    Sales.SalesOrder AS o ON o.OrderID = p.OrderID
    WHERE   o.CustomerID = @CustomerID
);
GO

SELECT * FROM Lab.fn_CustomerTimeline(35) ORDER BY EventDate, EventType, OrderID;

SELECT * FROM Lab.fn_CustomerTimeline_MS(35) EXCEPT SELECT * FROM Lab.fn_CustomerTimeline(35);
SELECT * FROM Lab.fn_CustomerTimeline(35)    EXCEPT SELECT * FROM Lab.fn_CustomerTimeline_MS(35);

CREATE OR ALTER FUNCTION Lab.fn_DaysSinceOrderToday (@OrderDate date)
RETURNS int
AS
BEGIN
    RETURN DATEDIFF(DAY, @OrderDate, CAST(GETDATE() AS date));
END;
GO

CREATE OR ALTER FUNCTION Lab.fn_DaysBetween (@From date, @To date)
RETURNS int
WITH SCHEMABINDING
AS
BEGIN
    RETURN DATEDIFF(DAY, @From, @To);
END;
GO

SELECT OBJECT_NAME(object_id) AS FunctionName, is_inlineable
FROM   sys.sql_modules
WHERE  object_id IN (OBJECT_ID('Lab.fn_DaysSinceOrderToday'), OBJECT_ID('Lab.fn_DaysBetween'));

CREATE OR ALTER FUNCTION Lab.fn_IsValidEmail (@Email nvarchar(255))
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
    RETURN CASE WHEN REGEXP_LIKE(LOWER(TRIM(@Email)), '^[a-z0-9._%+-]+@[a-z0-9.-]+\.[a-z]{2,}$')
                THEN 1 ELSE 0 END;
END;
GO

DROP TABLE IF EXISTS Lab.ContactSignup;
CREATE TABLE Lab.ContactSignup
(
    SignupID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
    Email    nvarchar(255)     NOT NULL,
    CONSTRAINT CK_ContactSignup_Email CHECK (Lab.fn_IsValidEmail(Email) = 1)
);
GO

-- Set-based preview of what would pass
SELECT ImportRowID, Email, Lab.fn_IsValidEmail(Email) AS IsValid
FROM   Staging.CustomerImport
ORDER BY ImportRowID;

-- One insert per row, counting outcomes (errors are handled properly on Day 20)
DECLARE @id int = 1, @ok int = 0, @failed int = 0, @e nvarchar(255);
WHILE @id <= 15
BEGIN
    SELECT @e = Email FROM Staging.CustomerImport WHERE ImportRowID = @id;
    BEGIN TRY
        INSERT INTO Lab.ContactSignup (Email) VALUES (@e);
        SET @ok += 1;
    END TRY
    BEGIN CATCH
        SET @failed += 1;
    END CATCH;
    SET @id += 1;
END;
SELECT @ok AS Accepted, @failed AS Rejected;

H. Knowledge check

1. Understanding. Why can’t a function INSERT into a permanent table?

2. Query interpretation. A report calls a scalar function that reads Sales.Payment for each of 3 million order rows. Before SQL Server 2019 this was very slow. Why, and what are two ways to improve it?

3. Debugging. ALTER TABLE … ADD Margin AS Lab.fn_X(ListPrice, StandardCost) PERSISTED; fails with a message about the column being non-deterministic. What do you check?

4. Understanding. Name two reasons an inline TVF usually outperforms a multi-statement TVF.

5. Debugging. DROP TABLE Lab.Calendar; fails: Cannot DROP TABLE ‘Lab.Calendar’ because it is being referenced by object ‘fn_WorkingDaysBetween’. Why, and what is the correct order of operations?

I. Troubleshooting and cleanup

Cleanup. Drop today’s objects in dependency order. This is required before you re-run the Day 12 script in future (it drops Lab.Calendar):

DROP TABLE    IF EXISTS Lab.ContactSignup;          -- depends on fn_IsValidEmail
DROP TABLE    IF EXISTS Lab.Product;                -- depends on fn_MarginPct
DROP FUNCTION IF EXISTS Lab.fn_IsValidEmail, Lab.fn_MarginPct, Lab.fn_LineNet, Lab.fn_PriceBand,
                        Lab.fn_OrdersBetween, Lab.fn_OrdersBetween_MS, Lab.fn_WorkingDaysBetween,
                        Lab.tvf_WorkingDaysBetween, Lab.fn_TopProducts, Lab.fn_CustomerTimeline_MS,
                        Lab.fn_CustomerTimeline, Lab.fn_DaysSinceOrderToday, Lab.fn_DaysBetween;

Keep the Day 12 objects and the Day 17 procedures. Core data is unchanged.

J. Completion checklist, recap and references

Completion checklist

• Worked example 1 showed IsDeterministic 1 and is_inlineable 1 for Lab.fn_LineNet.
• Inline and multi-statement versions both returned 198 orders and 63741.94.
• Your price-band tests all passed and the bands counted 8 / 10 / 12.
• Lab.fn_TopProducts returned the six rows in the lab.
• Tasks 1–4 match the success criteria; you ran the cleanup in order.

Recap

• Scalar functions return a value; inline TVFs are parameterised queries; multi-statement TVFs fill a table variable.
• Functions can’t have side effects: no data changes, dynamic SQL, THROW, NEWID().
• SCHEMABINDING protects dependencies and is needed for determinism; deterministic functions can drive persisted computed columns.
• SQL Server 2019+ can inline qualifying scalar functions; check sys.sql_modules.is_inlineable. Time-dependent functions and use in constraints/computed columns prevent inlining.
• Prefer inline TVFs over multi-statement TVFs; most multi-step logic fits in one query.

Next: Day 19 — Triggers: AFTER and INSTEAD OF triggers, the inserted/deleted tables, and set-based logic that handles multi-row changes correctly.

References (Microsoft Learn)

• CREATE FUNCTION (Transact-SQL)
• User-defined functions
• Scalar UDF inlining
• Deterministic and nondeterministic functions
• Intelligent query processing in SQL databases
• sys.sql_modules
• Computed columns