Leveling Up Your T-SQL Game: UDFs, Row_Number Performance, Triggers & Real DBA Lessons

Modern SQL Server development isn’t just about writing SELECT * statements anymore. As systems grow and performance expectations rise, understanding how T-SQL functions, ranking queries, and triggers behave under the hood becomes essential. In this post, we’ll walk through some fundamental and advanced concepts that every database professional should master:

• Inline Table-Valued Functions (iTVFs)
• Multi-Statement Table-Valued Functions (MSTVFs)
• Scalar UDFs
• Filtering with ROW_NUMBER
• Common bad trigger habits (and how to avoid them)

---

1. Learn T-SQL Inline Table-Valued Functions

Inline table-valued functions (iTVFs) are often called “parameterized views” because they return a table and can be used in joins just like a table or view. Unlike other function types, iTVFs are optimized by the Query Optimizer as if they were part of the calling query【1】.

Basic Syntax:

CREATE FUNCTION dbo.GetOrdersByCustomer (@CustomerID INT)
RETURNS TABLE
AS
RETURN
(
    SELECT OrderID, OrderDate, TotalDue
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

Usage:

SELECT *
FROM dbo.GetOrdersByCustomer(11000);

✅ Why It Matters:

• No hidden temp tables
• Inline functions allow the optimizer to generate a single execution plan
• Typically more performant than scalar or multi-statement UDFs【2】

🧠 Pro Tip: Use iTVFs for most reusable business logic that returns tabular results.

---

2. Learn T-SQL Multi-Statement Table-Valued Functions

Multi-statement table-valued functions (MSTVFs) allow more complex logic but at a cost. SQL Server treats MSTVFs as black boxes, often leading to poor cardinality estimates (1 row assumed)【3】.

Example:

CREATE FUNCTION dbo.GetOrdersWithFlag (@CustomerID INT)
RETURNS @Result TABLE (OrderID INT, OrderDate DATE, IsHighValue BIT)
AS
BEGIN
    INSERT INTO @Result
    SELECT OrderID, OrderDate,
           CASE WHEN TotalDue > 1000 THEN 1 ELSE 0 END
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID;

    RETURN;
END;

Usage:

SELECT *
FROM dbo.GetOrdersWithFlag(11000);

⚠️ Watch Out:

• No parallelism
• Poor row estimates can kill performance on large tables
• Harder for optimizer to inline logic【4】

✅ When to Use: Only when your logic can’t fit in a single RETURN statement.

---

3. Learn T-SQL Scalar User Defined Functions (UDFs)

Scalar UDFs return a single value. Historically, these have been performance killers because they execute row-by-row, effectively serializing your query【5】.

Example:

CREATE FUNCTION dbo.GetTaxAmount (@Amount MONEY)
RETURNS MONEY
AS
BEGIN
    RETURN @Amount * 0.0825;
END;

SELECT OrderID, dbo.GetTaxAmount(TotalDue) AS Tax
FROM Sales.Orders;

📉 Why It’s Slow:

• Executed once per row
• No parallelism before SQL Server 2019
• Hard for the optimizer to combine with outer query plans

💡 SQL Server 2019+ introduced scalar UDF inlining, which can improve performance dramatically when certain conditions are met【6】.

---

4. A Little About ROW_NUMBER Filtering Performance

The ROW_NUMBER() window function is one of the most useful ranking tools in T-SQL. It’s often used for pagination, deduplication, and picking top-N rows. But it’s not free.

Example:

WITH Ordered AS
(
    SELECT
        OrderID,
        ROW_NUMBER() OVER (PARTITION BY CustomerID ORDER BY OrderDate DESC) AS rn
    FROM Sales.Orders
)
SELECT *
FROM Ordered
WHERE rn = 1;

📊 Performance Note:

• SQL Server has to sort all rows in each partition.
• Indexes can help significantly here (e.g., on (CustomerID, OrderDate DESC)).
• Filtering on rn = 1 is common but not always the fastest approach if the data set is large【7】.

✅ Alternative:
Use CROSS APPLY with TOP 1 per group for better performance in some cases.

---

5. Learn T-SQL: Bad Trigger Habits

Triggers are useful, but they’re often overused or poorly written, causing major performance issues【9】.

❌ Common Bad Habits:

• Doing row-by-row operations in triggers
• Not handling multi-row updates properly
• Calling scalar UDFs inside triggers
• Logging or ETL work in DML triggers

Bad Example:

CREATE TRIGGER trg_AfterInsertOrders
ON Sales.Orders
AFTER INSERT
AS
BEGIN
    DECLARE @OrderID INT;
    SELECT @OrderID = OrderID FROM INSERTED;
    EXEC dbo.SomeSlowUDF @OrderID;
END;

CREATE TRIGGER trg_AfterInsertOrders
ON Sales.Orders
AFTER INSERT
AS
BEGIN
    DECLARE @OrderID INT;
    SELECT @OrderID = OrderID FROM INSERTED;
    EXEC dbo.SomeSlowUDF @OrderID;
END;

✅ Better:

• Handle set-based operations.
• Move non-critical work out of triggers (e.g., to queues or background jobs).
• Keep trigger logic lean and auditable.

---

🧪 Hands-On Workshop: From Novice to Expert

Here’s a structured exercise path to build your skills with these features.

Step 1: Create an Inline Table-Valued Function

• Build a GetTopOrdersByCustomer iTVF
• Test with and without indexes
• Compare execution plans

Step 2: Rewrite the Same Logic as MSTVF

• Measure execution time and reads
• Observe row estimates in actual execution plan

Step 3: Add a Scalar UDF

• Call it inside a large SELECT statement
• Measure impact using STATISTICS IO/TIME

Step 4: Replace Scalar UDF with Inline Logic

• Re-run performance tests
• Document results

Step 5: Test ROW_NUMBER vs TOP 1 WITH TIES or CROSS APPLY

• Create a large dataset (e.g., 1M rows)
• Benchmark both approaches

Step 6: Simulate a Trigger with Poor Logic

• Insert multiple rows and watch performance
• Refactor to a set-based trigger

📈 Tools to Use:

• SQL Server Management Studio (SSMS)
• Query Store
• Actual Execution Plan
• SET STATISTICS IO, TIME
• Extended Events or Profiler (for deeper insight)

---

Final Thoughts

Understanding how SQL Server treats functions and triggers is key to writing scalable code. Inline functions give the optimizer the most room to work. Multi-statement functions and scalar UDFs can be convenient, but they come with performance baggage. Triggers should be used sparingly and carefully.

ROW_NUMBER is powerful, but not always the best tool for every filtering job. Always measure, compare, and test in your environment. The difference between “it works” and “it scales” often comes down to how well you understand these internals.

---

📚 References

1. Itzik Ben-Gan, T-SQL Querying
2. Paul White, “Inlining of T-SQL Functions”
3. Brent Ozar – Functions Performance
4. Microsoft Docs: CREATE FUNCTION
5. Kalen Delaney, Inside SQL Server 2019
6. Microsoft Docs: Scalar UDF Inlining
7. Itzik Ben-Gan, “Efficiently Retrieving the Top N Rows per Group”
8. Brent Ozar Unlimited, Office Hours Archive
9. Microsoft Docs: CREATE TRIGGER