Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

3 / 5

Table-Valued Functions

Functions That Return Tables

While scalar functions transform individual values, table-valued functions (TVFs) return entire result sets—virtual tables that can be queried, joined, and filtered just like physical tables or views. This capability fundamentally changes what functions can accomplish in SQL.

Consider the difference: a scalar function GetEmployeeAge(employee_id) returns a single integer for one employee. A table-valued function GetEmployeesByDepartment(dept_id) returns a complete result set—potentially hundreds of rows with multiple columns—that you can SELECT from, JOIN with other tables, or filter with WHERE clauses.

TVFs bridge the gap between procedural and set-based thinking. They allow you to encapsulate complex query logic, parameterized data retrieval, and sophisticated transformations while maintaining SQL's powerful set-based semantics. When designed correctly, TVFs enable code reuse without sacrificing performance—a combination that's notoriously difficult to achieve in database programming.

What You Will Learn

By the end of this page, you will understand the two types of table-valued functions: inline TVFs and multi-statement TVFs. You'll master when to use each type, learn critical performance implications, and develop the skills to design TVFs that the query optimizer can efficiently integrate into complex query plans.

Table-Valued Function Fundamentals

A table-valued function returns a result set that appears to the caller as a table. Unlike scalar functions that return a single value and can appear in SELECT lists or WHERE conditions, TVFs appear in the FROM clause of queries—exactly where you would reference a table or view.

This fundamental difference means TVFs participate in query planning as data sources rather than row-by-row computations. The optimizer can apply predicates to them, join them with other tables, and process their results through the full query execution machinery.

tvf_usage_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Table-valued functions appear in FROM clause
 
-- Basic TVF invocation
SELECT * 
FROM dbo.GetEmployeesByDepartment(10);
 
-- TVF with alias
SELECT e.EmployeeId, e.FullName, e.Salary
FROM dbo.GetEmployeesByDepartment(10) AS e
WHERE e.Salary > 50000;
 
-- TVF in JOIN operations
SELECT 
    d.DepartmentName,
    e.EmployeeId,
    e.FullName
FROM Departments d
CROSS APPLY dbo.GetEmployeesByDepartment(d.DepartmentId) AS e
WHERE d.IsActive = 1;
 
-- CROSS APPLY executes the TVF for each row from Departments
-- Each execution receives that row's DepartmentId as parameter
 
-- OUTER APPLY preserves rows even when TVF returns empty
SELECT 
    d.DepartmentName,
    ISNULL(e.EmployeeCount, 0) AS EmployeeCount
FROM Departments d
OUTER APPLY (
    SELECT COUNT(*) AS EmployeeCount
    FROM dbo.GetEmployeesByDepartment(d.DepartmentId)
) AS e;

TVF Characteristics

•Returns tabular data — Zero, one, or many rows with a defined column structure.
•Appears in FROM clause — Used like a table source, supporting aliases, column references, and predicates.
•Supports APPLY operator — CROSS APPLY and OUTER APPLY enable row-by-row TVF execution with outer table context.
•Two distinct types — Inline TVFs (single SELECT statement) and multi-statement TVFs (procedural with table variable).
•Parameterized data access — Unlike views, TVFs accept parameters, enabling dynamic filtering and transformation at call time.
•Schema definition — The return table's columns and types are explicitly declared, providing strong typing.

Inline Table-Valued Functions

Inline table-valued functions (iTVFs) are the performance champions of the TVF world. An inline TVF contains a single SELECT statement, and the query optimizer expands this SELECT directly into the calling query's execution plan. This inline expansion enables the optimizer to apply all its normal optimization techniques—predicate pushdown, join reordering, index selection—across both the TVF logic and the enclosing query.

Think of inline TVFs as parameterized views. Views encapsulate a SELECT statement that gets inlined into queries that reference them. Inline TVFs do the same thing but accept parameters, enabling runtime customization that static views cannot provide.

inline_tvf_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- SQL Server: Inline Table-Valued Function Syntax
CREATE OR ALTER FUNCTION dbo.GetOrdersByDateRange
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS TABLE  -- No column definition = inline TVF
AS
RETURN
(
    -- Single SELECT statement defines both structure and logic
    SELECT 
        o.OrderId,
        o.CustomerId,
        c.CustomerName,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerId = c.CustomerId
    WHERE o.OrderDate >= @StartDate 
      AND o.OrderDate <= @EndDate
);
GO
 
-- Usage: optimizer inlines this into the calling query
SELECT * 
FROM dbo.GetOrdersByDateRange('2024-01-01', '2024-03-31')
WHERE TotalAmount > 1000
ORDER BY OrderDate;
 
-- The optimizer sees and optimizes the complete query:
-- SELECT ... FROM Orders o JOIN Customers c ... WHERE date range AND TotalAmount > 1000
 
-- Multiple parameters with defaults
CREATE FUNCTION dbo.SearchProducts
(
    @CategoryId INT = NULL,
    @MinPrice DECIMAL(10,2) = NULL,
    @MaxPrice DECIMAL(10,2) = NULL,
    @SearchTerm NVARCHAR(100) = NULL
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        ProductId,
        ProductName,
        CategoryId,
        Price,
        Description,
        StockQuantity
    FROM Products
    WHERE 
        (@CategoryId IS NULL OR CategoryId = @CategoryId)
        AND (@MinPrice IS NULL OR Price >= @MinPrice)
        AND (@MaxPrice IS NULL OR Price <= @MaxPrice)
        AND (@SearchTerm IS NULL OR ProductName LIKE '%' + @SearchTerm + '%')
);
 
-- Dynamic filtering at call time
SELECT * FROM dbo.SearchProducts(@CategoryId = 5, @MinPrice = 10);
SELECT * FROM dbo.SearchProducts(@SearchTerm = 'Widget');

Performance Advantage: Query Plan Integration

When you call an inline TVF, the optimizer substitutes the function's SELECT statement into the calling query BEFORE optimization. This means predicates from the outer query can be pushed into the TVF, indexes can be chosen based on the complete picture, and join strategies consider all tables. This is fundamentally different from multi-statement TVFs. Always prefer inline TVFs when a single SELECT can express your logic.

Multi-Statement Table-Valued Functions

Multi-statement table-valued functions (msTVFs) provide procedural flexibility when a single SELECT cannot express the required logic. They declare a table variable, populate it through multiple statements (including loops, conditionals, and multiple INSERTs), and return the populated table.

However, this procedural power comes at a severe cost: the query optimizer treats msTVFs as black boxes. It cannot see inside, cannot push predicates into them, and must use fixed cardinality estimates (often 1 row in older SQL Server versions, 100 rows in newer versions). This frequently leads to suboptimal query plans.

multi_statement_tvf.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
-- SQL Server: Multi-Statement TVF Syntax
CREATE OR ALTER FUNCTION dbo.GetHierarchyPath
(
    @EmployeeId INT
)
RETURNS @Result TABLE  -- Named table variable = multi-statement TVF
(
    Level INT,
    EmployeeId INT,
    EmployeeName NVARCHAR(100),
    ManagerId INT,
    Path NVARCHAR(MAX)
)
AS
BEGIN
    -- Complex logic requiring multiple statements
    DECLARE @CurrentId INT = @EmployeeId;
    DECLARE @Level INT = 0;
    DECLARE @Path NVARCHAR(MAX) = '';
    
    -- Walk up the hierarchy
    WHILE @CurrentId IS NOT NULL
    BEGIN
        INSERT INTO @Result (Level, EmployeeId, EmployeeName, ManagerId, Path)
        SELECT 
            @Level,
            EmployeeId,
            EmployeeName,
            ManagerId,
            @Path
        FROM Employees
        WHERE EmployeeId = @CurrentId;
        
        -- Move to parent
        SELECT 
            @Path = EmployeeName + CASE WHEN @Path = '' THEN '' ELSE ' -> ' END + @Path,
            @CurrentId = ManagerId
        FROM Employees
        WHERE EmployeeId = @CurrentId;
        
        SET @Level = @Level + 1;
        
        -- Safety limit
        IF @Level > 50 BREAK;
    END;
    
    -- Update paths now that we have complete hierarchy
    UPDATE @Result
    SET Path = (SELECT TOP 1 Path FROM @Result WHERE Level = (SELECT MAX(Level) FROM @Result));
    
    RETURN;
END;
GO
 
-- Another example: generating date ranges
CREATE FUNCTION dbo.GenerateDateRange
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS @Dates TABLE
(
    DateValue DATE PRIMARY KEY,
    DayOfWeek INT,
    DayName VARCHAR(10),
    WeekNumber INT,
    MonthName VARCHAR(10),
    IsWeekend BIT,
    IsHoliday BIT  -- Could be populated from holiday table
)
AS
BEGIN
    DECLARE @Current DATE = @StartDate;
    
    WHILE @Current <= @EndDate
    BEGIN
        INSERT INTO @Dates (DateValue, DayOfWeek, DayName, WeekNumber, MonthName, IsWeekend, IsHoliday)
        VALUES (
            @Current,
            DATEPART(WEEKDAY, @Current),
            DATENAME(WEEKDAY, @Current),
            DATEPART(WEEK, @Current),
            DATENAME(MONTH, @Current),
            CASE WHEN DATEPART(WEEKDAY, @Current) IN (1, 7) THEN 1 ELSE 0 END,
            0  -- Default, could lookup from holiday table
        );
        
        SET @Current = DATEADD(DAY, 1, @Current);
    END;
    
    -- Mark holidays from lookup table
    UPDATE d
    SET IsHoliday = 1
    FROM @Dates d
    INNER JOIN Holidays h ON d.DateValue = h.HolidayDate;
    
    RETURN;
END;

Performance Warning: The Black Box Problem

Multi-statement TVFs are optimization barriers. The query optimizer: (1) Cannot push WHERE predicates into the TVF, (2) Uses fixed row estimates causing join strategy errors, (3) Cannot consider indexes within the TVF logic, (4) Must execute the entire TVF before the outer query can process rows. Use msTVFs only when inline TVFs or CTEs cannot express the logic.

Inline vs Multi-Statement TVF Comparison
Characteristic	Inline TVF	Multi-Statement TVF
Syntax indicator	RETURNS TABLE (no columns)	RETURNS @var TABLE (columns defined)
Body structure	Single RETURN (SELECT...)	BEGIN...END with multiple statements
Optimization	Inlined into calling query	Black box; optimizer cannot see inside
Predicate pushdown	Yes	No
Cardinality estimates	Based on underlying tables	Fixed estimate (1 or 100 rows)
Parallel execution	Fully parallelizable	Serial only
Use when	Single SELECT suffices	Loop/conditional logic required

APPLY Operators with TVFs

The APPLY operator is the gateway to TVF power. APPLY executes a TVF once for each row from the left table, passing column values as parameters to the TVF. This row-by-row correlation enables scenarios that are awkward or impossible with standard JOINs.

Two forms exist: CROSS APPLY (equivalent to INNER JOIN semantics—omits left rows when TVF returns empty) and OUTER APPLY (equivalent to LEFT JOIN semantics—preserves left rows with NULL for TVF columns when empty).

apply_operator_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
-- Setup: Inline TVF for demonstration
CREATE FUNCTION dbo.GetTopOrdersForCustomer
(
    @CustomerId INT,
    @TopN INT = 5
)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@TopN)
        OrderId,
        OrderDate,
        TotalAmount,
        Status
    FROM Orders
    WHERE CustomerId = @CustomerId
    ORDER BY TotalAmount DESC
);
GO
 
-- CROSS APPLY: Get top 3 orders for each active customer
SELECT 
    c.CustomerId,
    c.CustomerName,
    o.OrderId,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetTopOrdersForCustomer(c.CustomerId, 3) AS o
WHERE c.IsActive = 1;
 
-- Customers with no orders are EXCLUDED from results (INNER join behavior)
 
-- OUTER APPLY: Include customers even with no orders
SELECT 
    c.CustomerId,
    c.CustomerName,
    o.OrderId,
    o.OrderDate,
    ISNULL(o.TotalAmount, 0) AS TotalAmount
FROM Customers c
OUTER APPLY dbo.GetTopOrdersForCustomer(c.CustomerId, 3) AS o
WHERE c.IsActive = 1;
 
-- Customers with no orders have NULL in order columns (LEFT join behavior)
 
-- APPLY with inline subquery (no separate TVF needed)
SELECT 
    d.DepartmentId,
    d.DepartmentName,
    empStats.EmployeeCount,
    empStats.AvgSalary,
    empStats.MaxSalary
FROM Departments d
CROSS APPLY (
    SELECT 
        COUNT(*) AS EmployeeCount,
        AVG(Salary) AS AvgSalary,
        MAX(Salary) AS MaxSalary
    FROM Employees e
    WHERE e.DepartmentId = d.DepartmentId
) AS empStats;
 
-- Powerful pattern: APPLY for "TOP N per group"
-- Get latest 3 orders per customer (a classic problem)
SELECT 
    c.CustomerId,
    c.CustomerName,
    o.OrderId,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
CROSS APPLY (
    SELECT TOP 3 OrderId, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerId = c.CustomerId
    ORDER BY OrderDate DESC
) AS o;

APPLY vs JOIN: When to Use Which

Use APPLY when: (1) Calling a TVF that needs values from the left table, (2) Implementing 'TOP N per group' queries, (3) Parsing complex columns (JSON, XML, comma-separated values), (4) Correlating to a subquery that's simpler expressed inline. Use JOIN for static relationships between tables without row-by-row customization.

PostgreSQL Set-Returning Functions

PostgreSQL uses different terminology: set-returning functions (SRFs) produce multiple rows. The most common pattern uses RETURNS TABLE or RETURNS SETOF. PostgreSQL automatically integrates SRFs into query plans without the inline vs multi-statement distinction that SQL Server requires.

PostgreSQL's approach is generally simpler: you write a function that returns a set, and the optimizer handles it based on the function's volatility and implementation language.

postgresql_srf.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
-- PostgreSQL: RETURNS TABLE syntax
CREATE OR REPLACE FUNCTION get_orders_by_status(order_status TEXT)
RETURNS TABLE (
    order_id INTEGER,
    customer_id INTEGER,
    order_date DATE,
    total_amount NUMERIC
)
LANGUAGE SQL
STABLE  -- Same result within transaction
AS $$
    SELECT order_id, customer_id, order_date, total_amount
    FROM orders
    WHERE status = order_status;
$$;
 
-- Usage
SELECT * FROM get_orders_by_status('PENDING');
 
-- RETURNS SETOF for existing type
CREATE OR REPLACE FUNCTION active_employees()
RETURNS SETOF employees  -- Returns rows matching employees table structure
LANGUAGE SQL
STABLE
AS $$
    SELECT * FROM employees WHERE is_active = true;
$$;
 
-- Usage with lateral join (PostgreSQL equivalent of APPLY)
SELECT 
    d.department_id,
    d.department_name,
    e.employee_name,
    e.salary
FROM departments d
CROSS JOIN LATERAL get_employees_by_dept(d.department_id) AS e;
 
-- LEFT JOIN LATERAL preserves departments with no employees
SELECT 
    d.department_id,
    d.department_name,
    e.employee_name
FROM departments d
LEFT JOIN LATERAL get_employees_by_dept(d.department_id) AS e ON true;
 
-- PL/pgSQL with procedural logic (like multi-statement TVF)
CREATE OR REPLACE FUNCTION generate_date_series(
    start_date DATE,
    end_date DATE
)
RETURNS TABLE (
    date_value DATE,
    day_name TEXT,
    is_weekend BOOLEAN
)
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    current_date DATE := start_date;
BEGIN
    WHILE current_date <= end_date LOOP
        date_value := current_date;
        day_name := TO_CHAR(current_date, 'Day');
        is_weekend := EXTRACT(DOW FROM current_date) IN (0, 6);
        
        RETURN NEXT;  -- Yield this row to the result set
        
        current_date := current_date + INTERVAL '1 day';
    END LOOP;
    
    RETURN;
END;
$$;
 
-- Simpler using generate_series (PostgreSQL built-in SRF)
SELECT 
    d::DATE AS date_value,
    TO_CHAR(d, 'Day') AS day_name,
    EXTRACT(DOW FROM d) IN (0, 6) AS is_weekend
FROM generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day') AS d;

PostgreSQL's generate_series and unnest

PostgreSQL provides powerful built-in SRFs: generate_series() creates number or date sequences, unnest() expands arrays into rows, json_each/jsonb_each() expands JSON objects. These are highly optimized and often preferred over custom SRFs for common operations.

TVF Design Patterns

Effective TVF design requires understanding common patterns that arise in real-world database applications. These patterns, when implemented as inline TVFs where possible, provide reusable building blocks for complex queries.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Pattern: Parameterized View
-- Use TVF when you need a view with runtime parameters
 
-- Instead of a view that everyone must filter themselves:
CREATE VIEW v_active_orders AS
SELECT * FROM orders WHERE status = 'ACTIVE';
-- Caller: SELECT * FROM v_active_orders WHERE region = 'EAST'
 
-- Create a parameterized TVF:
CREATE FUNCTION dbo.GetOrdersByStatusAndRegion
(
    @Status VARCHAR(20),
    @Region VARCHAR(50) = NULL  -- Optional parameter
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        o.OrderId,
        o.CustomerId,
        o.OrderDate,
        o.TotalAmount,
        o.Status,
        o.Region
    FROM Orders o
    WHERE o.Status = @Status
      AND (@Region IS NULL OR o.Region = @Region)
);
 
-- Callers get exactly what they need:
SELECT * FROM dbo.GetOrdersByStatusAndRegion('ACTIVE', 'EAST');
SELECT * FROM dbo.GetOrdersByStatusAndRegion('PENDING', NULL);  -- All regions

Performance Optimization

TVF performance optimization centers on one critical principle: enable the query optimizer to do its job. Inline TVFs naturally support this; multi-statement TVFs actively prevent it. Beyond this fundamental choice, several techniques can improve TVF performance.

Performance Best Practices

•Always prefer inline TVFs — If a single SELECT can express your logic, use inline. The performance difference can be 10-100x.
•Avoid multi-statement TVFs in frequently-executed queries — The cardinality estimation problem causes cascading poor join choices.
•Use OPTION (RECOMPILE) with msTVFs carefully — Can help with parameter sniffing but adds compilation overhead.
•Consider interleaved execution (SQL Server 2017+) — Enables runtime cardinality feedback for msTVFs.
•Index underlying tables appropriately — TVFs inherit performance from the tables they query; ensure proper indexing.
•Minimize TVF complexity — Complex TVFs are hard to debug and optimize; keep them focused.

tvf_performance_analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Analyze TVF execution plan
 
-- Enable execution plan
SET STATISTICS XML ON;
 
-- Inline TVF: Check for plan integration
SELECT o.OrderId, o.CustomerName, o.TotalAmount
FROM dbo.GetOrdersByDateRange('2024-01-01', '2024-12-31') AS o
WHERE o.TotalAmount > 500;
 
-- In the execution plan:
-- ✓ Inline TVF: See predicates pushed into the scan/seek
-- ✗ msTVF: See Table Valued Function operator as a black box
 
-- Check cardinality estimates vs actual
-- For msTVFs, estimated rows are often 1 or 100 (fixed)
-- Actual rows may be thousands, causing bad join strategies
 
-- SQL Server 2017+: Interleaved Execution
-- Optimizer runs msTVF first to get actual cardinality
-- Then re-optimizes the rest of the plan
 
-- Force interleaved execution in testing
SELECT o.OrderId
FROM dbo.SomeMultiStatementTVF(10) AS tvf
INNER JOIN Orders o ON tvf.OrderId = o.OrderId
OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));
 
-- Convert msTVF to inline TVF (example refactoring)
 
-- BEFORE: Multi-statement with loop (slow)
CREATE FUNCTION dbo.GetDateRangeOld(@Start DATE, @End DATE)
RETURNS @Dates TABLE (DateValue DATE)
AS
BEGIN
    WHILE @Start <= @End
    BEGIN
        INSERT INTO @Dates VALUES (@Start);
        SET @Start = DATEADD(DAY, 1, @Start);
    END;
    RETURN;
END;
 
-- AFTER: Inline using numbers table (fast)
CREATE FUNCTION dbo.GetDateRangeNew(@Start DATE, @End DATE)
RETURNS TABLE
AS
RETURN
(
    SELECT DATEADD(DAY, n.n, @Start) AS DateValue
    FROM (
        SELECT TOP (DATEDIFF(DAY, @Start, @End) + 1)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.objects a CROSS JOIN sys.objects b
    ) AS n
);

The Multi-Statement TVF Tax

In production workloads, converting a frequently-called msTVF to an inline TVF often reduces query time by 90% or more. If you must use msTVFs, consider caching results in a temporary table before joining to avoid repeated execution, or use SQL Server 2017+ interleaved execution.

Real-World Examples

Let's examine production-quality TVFs that solve common business requirements. Each example demonstrates design decisions, trade-offs, and optimization considerations.

production_tvf_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
-- =====================================================
-- EXAMPLE 1: Configurable Search with Pagination
-- =====================================================
CREATE FUNCTION dbo.SearchOrdersAdvanced
(
    @CustomerId INT = NULL,
    @Status VARCHAR(20) = NULL,
    @MinAmount DECIMAL(18,2) = NULL,
    @MaxAmount DECIMAL(18,2) = NULL,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL,
    @SearchText NVARCHAR(100) = NULL,
    @PageNumber INT = 1,
    @PageSize INT = 25,
    @SortColumn VARCHAR(50) = 'OrderDate',
    @SortDirection VARCHAR(4) = 'DESC'
)
RETURNS TABLE
AS
RETURN
(
    WITH FilteredOrders AS (
        SELECT 
            o.OrderId,
            o.CustomerId,
            c.CustomerName,
            o.OrderDate,
            o.TotalAmount,
            o.Status,
            o.Notes,
            COUNT(*) OVER () AS TotalCount  -- Total matching rows
        FROM Orders o
        INNER JOIN Customers c ON o.CustomerId = c.CustomerId
        WHERE 
            (@CustomerId IS NULL OR o.CustomerId = @CustomerId)
            AND (@Status IS NULL OR o.Status = @Status)
            AND (@MinAmount IS NULL OR o.TotalAmount >= @MinAmount)
            AND (@MaxAmount IS NULL OR o.TotalAmount <= @MaxAmount)
            AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
            AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)
            AND (@SearchText IS NULL 
                 OR c.CustomerName LIKE '%' + @SearchText + '%'
                 OR o.Notes LIKE '%' + @SearchText + '%')
    ),
    SortedOrders AS (
        SELECT *,
            ROW_NUMBER() OVER (
                ORDER BY 
                    CASE WHEN @SortColumn = 'OrderDate' AND @SortDirection = 'ASC' THEN OrderDate END,
                    CASE WHEN @SortColumn = 'OrderDate' AND @SortDirection = 'DESC' THEN OrderDate END DESC,
                    CASE WHEN @SortColumn = 'TotalAmount' AND @SortDirection = 'ASC' THEN TotalAmount END,
                    CASE WHEN @SortColumn = 'TotalAmount' AND @SortDirection = 'DESC' THEN TotalAmount END DESC,
                    CASE WHEN @SortColumn = 'CustomerName' AND @SortDirection = 'ASC' THEN CustomerName END,
                    CASE WHEN @SortColumn = 'CustomerName' AND @SortDirection = 'DESC' THEN CustomerName END DESC,
                    OrderId  -- Stable sort tiebreaker
            ) AS RowNum
        FROM FilteredOrders
    )
    SELECT 
        OrderId,
        CustomerId,
        CustomerName,
        OrderDate,
        TotalAmount,
        Status,
        Notes,
        TotalCount,
        @PageNumber AS CurrentPage,
        CEILING(TotalCount * 1.0 / @PageSize) AS TotalPages
    FROM SortedOrders
    WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize + 1) 
                     AND (@PageNumber * @PageSize)
);
 
-- Usage: Paginated, filtered, sorted search
SELECT * FROM dbo.SearchOrdersAdvanced(
    @Status = 'PENDING',
    @MinAmount = 100,
    @PageNumber = 2,
    @PageSize = 20,
    @SortColumn = 'TotalAmount',
    @SortDirection = 'DESC'
);
 
-- =====================================================
-- EXAMPLE 2: JSON Array Expansion
-- =====================================================
CREATE FUNCTION dbo.ParseJsonArray(@JsonArray NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
    SELECT 
        [key] AS ArrayIndex,
        value AS ArrayValue,
        JSON_VALUE(value, '$.id') AS Id,
        JSON_VALUE(value, '$.name') AS Name,
        JSON_VALUE(value, '$.amount') AS Amount
    FROM OPENJSON(@JsonArray)
);
 
-- Usage: Expand JSON array into rows
SELECT ar.ArrayIndex, ar.Id, ar.Name, ar.Amount
FROM dbo.ParseJsonArray('[
    {"id": 1, "name": "Item A", "amount": 100},
    {"id": 2, "name": "Item B", "amount": 200},
    {"id": 3, "name": "Item C", "amount": 150}
]') AS ar;
 
-- =====================================================
-- EXAMPLE 3: Running Balance Calculation
-- =====================================================
CREATE FUNCTION dbo.GetAccountStatement
(
    @AccountId INT,
    @StartDate DATE,
    @EndDate DATE
)
RETURNS TABLE
AS
RETURN
(
    WITH Transactions AS (
        SELECT 
            TransactionId,
            TransactionDate,
            Description,
            CASE 
                WHEN TransactionType = 'CREDIT' THEN Amount
                ELSE 0 
            END AS Credits,
            CASE 
                WHEN TransactionType = 'DEBIT' THEN Amount
                ELSE 0 
            END AS Debits,
            CASE 
                WHEN TransactionType = 'CREDIT' THEN Amount
                ELSE -Amount 
            END AS NetAmount
        FROM AccountTransactions
        WHERE AccountId = @AccountId
          AND TransactionDate BETWEEN @StartDate AND @EndDate
    )
    SELECT 
        t.TransactionId,
        t.TransactionDate,
        t.Description,
        t.Credits,
        t.Debits,
        SUM(t.NetAmount) OVER (ORDER BY t.TransactionDate, t.TransactionId 
                               ROWS UNBOUNDED PRECEDING) AS RunningBalance
    FROM Transactions t
);
 
-- Usage: Generate bank-style statement
SELECT * FROM dbo.GetAccountStatement(12345, '2024-01-01', '2024-01-31')
ORDER BY TransactionDate, TransactionId;

Summary: Mastering Table-Valued Functions

Table-valued functions expand what functions can accomplish in SQL by returning complete result sets. The distinction between inline and multi-statement TVFs is the most critical performance consideration in TVF development.

Key Takeaways

•TVFs return result sets — They appear in FROM clauses and can be joined, filtered, and aggregated like tables.
•Inline TVFs are parameterized views — The optimizer fully integrates them into query plans; always prefer them when possible.
•Multi-statement TVFs are procedural black boxes — Use only when loops or complex logic require it; expect performance overhead.
•APPLY enables row-by-row TVF execution — CROSS APPLY and OUTER APPLY correlate TVF calls to outer table rows.
•PostgreSQL uses set-returning functions — Similar concepts with RETURNS TABLE and LATERAL joins providing equivalent functionality.
•Design patterns include parameterized views, string splitting, and hierarchy traversal — These reusable patterns solve common problems efficiently.

Page Complete

You now understand table-valued functions comprehensively—from inline expansion to multi-statement procedural logic. The next page explores deterministic functions and how function purity affects optimization, indexing, and result consistency.

3 / 5

Loading learning content...

Database Management SystemsSQL Advanced Features

SQL Functions

LevelIntermediate

Duration75 mins

TopicSQL Advanced Features

3 / 5

Table-Valued Functions

Functions That Return Tables

What You Will Learn

Table-Valued Function Fundamentals

tvf_usage_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
-- Table-valued functions appear in FROM clause
 
-- Basic TVF invocation
SELECT * 
FROM dbo.GetEmployeesByDepartment(10);
 
-- TVF with alias
SELECT e.EmployeeId, e.FullName, e.Salary
FROM dbo.GetEmployeesByDepartment(10) AS e
WHERE e.Salary > 50000;
 
-- TVF in JOIN operations
SELECT 
    d.DepartmentName,
    e.EmployeeId,
    e.FullName
FROM Departments d
CROSS APPLY dbo.GetEmployeesByDepartment(d.DepartmentId) AS e
WHERE d.IsActive = 1;
 
-- CROSS APPLY executes the TVF for each row from Departments
-- Each execution receives that row's DepartmentId as parameter
 
-- OUTER APPLY preserves rows even when TVF returns empty
SELECT 
    d.DepartmentName,
    ISNULL(e.EmployeeCount, 0) AS EmployeeCount
FROM Departments d
OUTER APPLY (
    SELECT COUNT(*) AS EmployeeCount
    FROM dbo.GetEmployeesByDepartment(d.DepartmentId)
) AS e;

TVF Characteristics

•Returns tabular data — Zero, one, or many rows with a defined column structure.
•Appears in FROM clause — Used like a table source, supporting aliases, column references, and predicates.
•Supports APPLY operator — CROSS APPLY and OUTER APPLY enable row-by-row TVF execution with outer table context.
•Two distinct types — Inline TVFs (single SELECT statement) and multi-statement TVFs (procedural with table variable).
•Parameterized data access — Unlike views, TVFs accept parameters, enabling dynamic filtering and transformation at call time.
•Schema definition — The return table's columns and types are explicitly declared, providing strong typing.

Inline Table-Valued Functions

inline_tvf_syntax.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
-- SQL Server: Inline Table-Valued Function Syntax
CREATE OR ALTER FUNCTION dbo.GetOrdersByDateRange
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS TABLE  -- No column definition = inline TVF
AS
RETURN
(
    -- Single SELECT statement defines both structure and logic
    SELECT 
        o.OrderId,
        o.CustomerId,
        c.CustomerName,
        o.OrderDate,
        o.TotalAmount,
        o.Status
    FROM Orders o
    INNER JOIN Customers c ON o.CustomerId = c.CustomerId
    WHERE o.OrderDate >= @StartDate 
      AND o.OrderDate <= @EndDate
);
GO
 
-- Usage: optimizer inlines this into the calling query
SELECT * 
FROM dbo.GetOrdersByDateRange('2024-01-01', '2024-03-31')
WHERE TotalAmount > 1000
ORDER BY OrderDate;
 
-- The optimizer sees and optimizes the complete query:
-- SELECT ... FROM Orders o JOIN Customers c ... WHERE date range AND TotalAmount > 1000
 
-- Multiple parameters with defaults
CREATE FUNCTION dbo.SearchProducts
(
    @CategoryId INT = NULL,
    @MinPrice DECIMAL(10,2) = NULL,
    @MaxPrice DECIMAL(10,2) = NULL,
    @SearchTerm NVARCHAR(100) = NULL
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        ProductId,
        ProductName,
        CategoryId,
        Price,
        Description,
        StockQuantity
    FROM Products
    WHERE 
        (@CategoryId IS NULL OR CategoryId = @CategoryId)
        AND (@MinPrice IS NULL OR Price >= @MinPrice)
        AND (@MaxPrice IS NULL OR Price <= @MaxPrice)
        AND (@SearchTerm IS NULL OR ProductName LIKE '%' + @SearchTerm + '%')
);
 
-- Dynamic filtering at call time
SELECT * FROM dbo.SearchProducts(@CategoryId = 5, @MinPrice = 10);
SELECT * FROM dbo.SearchProducts(@SearchTerm = 'Widget');

Performance Advantage: Query Plan Integration

Multi-Statement Table-Valued Functions

multi_statement_tvf.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
-- SQL Server: Multi-Statement TVF Syntax
CREATE OR ALTER FUNCTION dbo.GetHierarchyPath
(
    @EmployeeId INT
)
RETURNS @Result TABLE  -- Named table variable = multi-statement TVF
(
    Level INT,
    EmployeeId INT,
    EmployeeName NVARCHAR(100),
    ManagerId INT,
    Path NVARCHAR(MAX)
)
AS
BEGIN
    -- Complex logic requiring multiple statements
    DECLARE @CurrentId INT = @EmployeeId;
    DECLARE @Level INT = 0;
    DECLARE @Path NVARCHAR(MAX) = '';
    
    -- Walk up the hierarchy
    WHILE @CurrentId IS NOT NULL
    BEGIN
        INSERT INTO @Result (Level, EmployeeId, EmployeeName, ManagerId, Path)
        SELECT 
            @Level,
            EmployeeId,
            EmployeeName,
            ManagerId,
            @Path
        FROM Employees
        WHERE EmployeeId = @CurrentId;
        
        -- Move to parent
        SELECT 
            @Path = EmployeeName + CASE WHEN @Path = '' THEN '' ELSE ' -> ' END + @Path,
            @CurrentId = ManagerId
        FROM Employees
        WHERE EmployeeId = @CurrentId;
        
        SET @Level = @Level + 1;
        
        -- Safety limit
        IF @Level > 50 BREAK;
    END;
    
    -- Update paths now that we have complete hierarchy
    UPDATE @Result
    SET Path = (SELECT TOP 1 Path FROM @Result WHERE Level = (SELECT MAX(Level) FROM @Result));
    
    RETURN;
END;
GO
 
-- Another example: generating date ranges
CREATE FUNCTION dbo.GenerateDateRange
(
    @StartDate DATE,
    @EndDate DATE
)
RETURNS @Dates TABLE
(
    DateValue DATE PRIMARY KEY,
    DayOfWeek INT,
    DayName VARCHAR(10),
    WeekNumber INT,
    MonthName VARCHAR(10),
    IsWeekend BIT,
    IsHoliday BIT  -- Could be populated from holiday table
)
AS
BEGIN
    DECLARE @Current DATE = @StartDate;
    
    WHILE @Current <= @EndDate
    BEGIN
        INSERT INTO @Dates (DateValue, DayOfWeek, DayName, WeekNumber, MonthName, IsWeekend, IsHoliday)
        VALUES (
            @Current,
            DATEPART(WEEKDAY, @Current),
            DATENAME(WEEKDAY, @Current),
            DATEPART(WEEK, @Current),
            DATENAME(MONTH, @Current),
            CASE WHEN DATEPART(WEEKDAY, @Current) IN (1, 7) THEN 1 ELSE 0 END,
            0  -- Default, could lookup from holiday table
        );
        
        SET @Current = DATEADD(DAY, 1, @Current);
    END;
    
    -- Mark holidays from lookup table
    UPDATE d
    SET IsHoliday = 1
    FROM @Dates d
    INNER JOIN Holidays h ON d.DateValue = h.HolidayDate;
    
    RETURN;
END;

Performance Warning: The Black Box Problem

Inline vs Multi-Statement TVF Comparison
Characteristic	Inline TVF	Multi-Statement TVF
Syntax indicator	RETURNS TABLE (no columns)	RETURNS @var TABLE (columns defined)
Body structure	Single RETURN (SELECT...)	BEGIN...END with multiple statements
Optimization	Inlined into calling query	Black box; optimizer cannot see inside
Predicate pushdown	Yes	No
Cardinality estimates	Based on underlying tables	Fixed estimate (1 or 100 rows)
Parallel execution	Fully parallelizable	Serial only
Use when	Single SELECT suffices	Loop/conditional logic required

APPLY Operators with TVFs

apply_operator_patterns.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
-- Setup: Inline TVF for demonstration
CREATE FUNCTION dbo.GetTopOrdersForCustomer
(
    @CustomerId INT,
    @TopN INT = 5
)
RETURNS TABLE
AS
RETURN
(
    SELECT TOP (@TopN)
        OrderId,
        OrderDate,
        TotalAmount,
        Status
    FROM Orders
    WHERE CustomerId = @CustomerId
    ORDER BY TotalAmount DESC
);
GO
 
-- CROSS APPLY: Get top 3 orders for each active customer
SELECT 
    c.CustomerId,
    c.CustomerName,
    o.OrderId,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
CROSS APPLY dbo.GetTopOrdersForCustomer(c.CustomerId, 3) AS o
WHERE c.IsActive = 1;
 
-- Customers with no orders are EXCLUDED from results (INNER join behavior)
 
-- OUTER APPLY: Include customers even with no orders
SELECT 
    c.CustomerId,
    c.CustomerName,
    o.OrderId,
    o.OrderDate,
    ISNULL(o.TotalAmount, 0) AS TotalAmount
FROM Customers c
OUTER APPLY dbo.GetTopOrdersForCustomer(c.CustomerId, 3) AS o
WHERE c.IsActive = 1;
 
-- Customers with no orders have NULL in order columns (LEFT join behavior)
 
-- APPLY with inline subquery (no separate TVF needed)
SELECT 
    d.DepartmentId,
    d.DepartmentName,
    empStats.EmployeeCount,
    empStats.AvgSalary,
    empStats.MaxSalary
FROM Departments d
CROSS APPLY (
    SELECT 
        COUNT(*) AS EmployeeCount,
        AVG(Salary) AS AvgSalary,
        MAX(Salary) AS MaxSalary
    FROM Employees e
    WHERE e.DepartmentId = d.DepartmentId
) AS empStats;
 
-- Powerful pattern: APPLY for "TOP N per group"
-- Get latest 3 orders per customer (a classic problem)
SELECT 
    c.CustomerId,
    c.CustomerName,
    o.OrderId,
    o.OrderDate,
    o.TotalAmount
FROM Customers c
CROSS APPLY (
    SELECT TOP 3 OrderId, OrderDate, TotalAmount
    FROM Orders
    WHERE CustomerId = c.CustomerId
    ORDER BY OrderDate DESC
) AS o;

APPLY vs JOIN: When to Use Which

PostgreSQL Set-Returning Functions

PostgreSQL's approach is generally simpler: you write a function that returns a set, and the optimizer handles it based on the function's volatility and implementation language.

postgresql_srf.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
-- PostgreSQL: RETURNS TABLE syntax
CREATE OR REPLACE FUNCTION get_orders_by_status(order_status TEXT)
RETURNS TABLE (
    order_id INTEGER,
    customer_id INTEGER,
    order_date DATE,
    total_amount NUMERIC
)
LANGUAGE SQL
STABLE  -- Same result within transaction
AS $$
    SELECT order_id, customer_id, order_date, total_amount
    FROM orders
    WHERE status = order_status;
$$;
 
-- Usage
SELECT * FROM get_orders_by_status('PENDING');
 
-- RETURNS SETOF for existing type
CREATE OR REPLACE FUNCTION active_employees()
RETURNS SETOF employees  -- Returns rows matching employees table structure
LANGUAGE SQL
STABLE
AS $$
    SELECT * FROM employees WHERE is_active = true;
$$;
 
-- Usage with lateral join (PostgreSQL equivalent of APPLY)
SELECT 
    d.department_id,
    d.department_name,
    e.employee_name,
    e.salary
FROM departments d
CROSS JOIN LATERAL get_employees_by_dept(d.department_id) AS e;
 
-- LEFT JOIN LATERAL preserves departments with no employees
SELECT 
    d.department_id,
    d.department_name,
    e.employee_name
FROM departments d
LEFT JOIN LATERAL get_employees_by_dept(d.department_id) AS e ON true;
 
-- PL/pgSQL with procedural logic (like multi-statement TVF)
CREATE OR REPLACE FUNCTION generate_date_series(
    start_date DATE,
    end_date DATE
)
RETURNS TABLE (
    date_value DATE,
    day_name TEXT,
    is_weekend BOOLEAN
)
LANGUAGE plpgsql
STABLE
AS $$
DECLARE
    current_date DATE := start_date;
BEGIN
    WHILE current_date <= end_date LOOP
        date_value := current_date;
        day_name := TO_CHAR(current_date, 'Day');
        is_weekend := EXTRACT(DOW FROM current_date) IN (0, 6);
        
        RETURN NEXT;  -- Yield this row to the result set
        
        current_date := current_date + INTERVAL '1 day';
    END LOOP;
    
    RETURN;
END;
$$;
 
-- Simpler using generate_series (PostgreSQL built-in SRF)
SELECT 
    d::DATE AS date_value,
    TO_CHAR(d, 'Day') AS day_name,
    EXTRACT(DOW FROM d) IN (0, 6) AS is_weekend
FROM generate_series('2024-01-01'::DATE, '2024-12-31'::DATE, '1 day') AS d;

PostgreSQL's generate_series and unnest

TVF Design Patterns

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
-- Pattern: Parameterized View
-- Use TVF when you need a view with runtime parameters
 
-- Instead of a view that everyone must filter themselves:
CREATE VIEW v_active_orders AS
SELECT * FROM orders WHERE status = 'ACTIVE';
-- Caller: SELECT * FROM v_active_orders WHERE region = 'EAST'
 
-- Create a parameterized TVF:
CREATE FUNCTION dbo.GetOrdersByStatusAndRegion
(
    @Status VARCHAR(20),
    @Region VARCHAR(50) = NULL  -- Optional parameter
)
RETURNS TABLE
AS
RETURN
(
    SELECT 
        o.OrderId,
        o.CustomerId,
        o.OrderDate,
        o.TotalAmount,
        o.Status,
        o.Region
    FROM Orders o
    WHERE o.Status = @Status
      AND (@Region IS NULL OR o.Region = @Region)
);
 
-- Callers get exactly what they need:
SELECT * FROM dbo.GetOrdersByStatusAndRegion('ACTIVE', 'EAST');
SELECT * FROM dbo.GetOrdersByStatusAndRegion('PENDING', NULL);  -- All regions

Performance Optimization

Performance Best Practices

•Always prefer inline TVFs — If a single SELECT can express your logic, use inline. The performance difference can be 10-100x.
•Avoid multi-statement TVFs in frequently-executed queries — The cardinality estimation problem causes cascading poor join choices.
•Use OPTION (RECOMPILE) with msTVFs carefully — Can help with parameter sniffing but adds compilation overhead.
•Consider interleaved execution (SQL Server 2017+) — Enables runtime cardinality feedback for msTVFs.
•Index underlying tables appropriately — TVFs inherit performance from the tables they query; ensure proper indexing.
•Minimize TVF complexity — Complex TVFs are hard to debug and optimize; keep them focused.

tvf_performance_analysis.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
-- Analyze TVF execution plan
 
-- Enable execution plan
SET STATISTICS XML ON;
 
-- Inline TVF: Check for plan integration
SELECT o.OrderId, o.CustomerName, o.TotalAmount
FROM dbo.GetOrdersByDateRange('2024-01-01', '2024-12-31') AS o
WHERE o.TotalAmount > 500;
 
-- In the execution plan:
-- ✓ Inline TVF: See predicates pushed into the scan/seek
-- ✗ msTVF: See Table Valued Function operator as a black box
 
-- Check cardinality estimates vs actual
-- For msTVFs, estimated rows are often 1 or 100 (fixed)
-- Actual rows may be thousands, causing bad join strategies
 
-- SQL Server 2017+: Interleaved Execution
-- Optimizer runs msTVF first to get actual cardinality
-- Then re-optimizes the rest of the plan
 
-- Force interleaved execution in testing
SELECT o.OrderId
FROM dbo.SomeMultiStatementTVF(10) AS tvf
INNER JOIN Orders o ON tvf.OrderId = o.OrderId
OPTION (USE HINT ('ENABLE_QUERY_OPTIMIZER_HOTFIXES'));
 
-- Convert msTVF to inline TVF (example refactoring)
 
-- BEFORE: Multi-statement with loop (slow)
CREATE FUNCTION dbo.GetDateRangeOld(@Start DATE, @End DATE)
RETURNS @Dates TABLE (DateValue DATE)
AS
BEGIN
    WHILE @Start <= @End
    BEGIN
        INSERT INTO @Dates VALUES (@Start);
        SET @Start = DATEADD(DAY, 1, @Start);
    END;
    RETURN;
END;
 
-- AFTER: Inline using numbers table (fast)
CREATE FUNCTION dbo.GetDateRangeNew(@Start DATE, @End DATE)
RETURNS TABLE
AS
RETURN
(
    SELECT DATEADD(DAY, n.n, @Start) AS DateValue
    FROM (
        SELECT TOP (DATEDIFF(DAY, @Start, @End) + 1)
            ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
        FROM sys.objects a CROSS JOIN sys.objects b
    ) AS n
);

The Multi-Statement TVF Tax

Real-World Examples

Let's examine production-quality TVFs that solve common business requirements. Each example demonstrates design decisions, trade-offs, and optimization considerations.

production_tvf_examples.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
-- =====================================================
-- EXAMPLE 1: Configurable Search with Pagination
-- =====================================================
CREATE FUNCTION dbo.SearchOrdersAdvanced
(
    @CustomerId INT = NULL,
    @Status VARCHAR(20) = NULL,
    @MinAmount DECIMAL(18,2) = NULL,
    @MaxAmount DECIMAL(18,2) = NULL,
    @StartDate DATE = NULL,
    @EndDate DATE = NULL,
    @SearchText NVARCHAR(100) = NULL,
    @PageNumber INT = 1,
    @PageSize INT = 25,
    @SortColumn VARCHAR(50) = 'OrderDate',
    @SortDirection VARCHAR(4) = 'DESC'
)
RETURNS TABLE
AS
RETURN
(
    WITH FilteredOrders AS (
        SELECT 
            o.OrderId,
            o.CustomerId,
            c.CustomerName,
            o.OrderDate,
            o.TotalAmount,
            o.Status,
            o.Notes,
            COUNT(*) OVER () AS TotalCount  -- Total matching rows
        FROM Orders o
        INNER JOIN Customers c ON o.CustomerId = c.CustomerId
        WHERE 
            (@CustomerId IS NULL OR o.CustomerId = @CustomerId)
            AND (@Status IS NULL OR o.Status = @Status)
            AND (@MinAmount IS NULL OR o.TotalAmount >= @MinAmount)
            AND (@MaxAmount IS NULL OR o.TotalAmount <= @MaxAmount)
            AND (@StartDate IS NULL OR o.OrderDate >= @StartDate)
            AND (@EndDate IS NULL OR o.OrderDate <= @EndDate)
            AND (@SearchText IS NULL 
                 OR c.CustomerName LIKE '%' + @SearchText + '%'
                 OR o.Notes LIKE '%' + @SearchText + '%')
    ),
    SortedOrders AS (
        SELECT *,
            ROW_NUMBER() OVER (
                ORDER BY 
                    CASE WHEN @SortColumn = 'OrderDate' AND @SortDirection = 'ASC' THEN OrderDate END,
                    CASE WHEN @SortColumn = 'OrderDate' AND @SortDirection = 'DESC' THEN OrderDate END DESC,
                    CASE WHEN @SortColumn = 'TotalAmount' AND @SortDirection = 'ASC' THEN TotalAmount END,
                    CASE WHEN @SortColumn = 'TotalAmount' AND @SortDirection = 'DESC' THEN TotalAmount END DESC,
                    CASE WHEN @SortColumn = 'CustomerName' AND @SortDirection = 'ASC' THEN CustomerName END,
                    CASE WHEN @SortColumn = 'CustomerName' AND @SortDirection = 'DESC' THEN CustomerName END DESC,
                    OrderId  -- Stable sort tiebreaker
            ) AS RowNum
        FROM FilteredOrders
    )
    SELECT 
        OrderId,
        CustomerId,
        CustomerName,
        OrderDate,
        TotalAmount,
        Status,
        Notes,
        TotalCount,
        @PageNumber AS CurrentPage,
        CEILING(TotalCount * 1.0 / @PageSize) AS TotalPages
    FROM SortedOrders
    WHERE RowNum BETWEEN ((@PageNumber - 1) * @PageSize + 1) 
                     AND (@PageNumber * @PageSize)
);
 
-- Usage: Paginated, filtered, sorted search
SELECT * FROM dbo.SearchOrdersAdvanced(
    @Status = 'PENDING',
    @MinAmount = 100,
    @PageNumber = 2,
    @PageSize = 20,
    @SortColumn = 'TotalAmount',
    @SortDirection = 'DESC'
);
 
-- =====================================================
-- EXAMPLE 2: JSON Array Expansion
-- =====================================================
CREATE FUNCTION dbo.ParseJsonArray(@JsonArray NVARCHAR(MAX))
RETURNS TABLE
AS
RETURN
(
    SELECT 
        [key] AS ArrayIndex,
        value AS ArrayValue,
        JSON_VALUE(value, '$.id') AS Id,
        JSON_VALUE(value, '$.name') AS Name,
        JSON_VALUE(value, '$.amount') AS Amount
    FROM OPENJSON(@JsonArray)
);
 
-- Usage: Expand JSON array into rows
SELECT ar.ArrayIndex, ar.Id, ar.Name, ar.Amount
FROM dbo.ParseJsonArray('[
    {"id": 1, "name": "Item A", "amount": 100},
    {"id": 2, "name": "Item B", "amount": 200},
    {"id": 3, "name": "Item C", "amount": 150}
]') AS ar;
 
-- =====================================================
-- EXAMPLE 3: Running Balance Calculation
-- =====================================================
CREATE FUNCTION dbo.GetAccountStatement
(
    @AccountId INT,
    @StartDate DATE,
    @EndDate DATE
)
RETURNS TABLE
AS
RETURN
(
    WITH Transactions AS (
        SELECT 
            TransactionId,
            TransactionDate,
            Description,
            CASE 
                WHEN TransactionType = 'CREDIT' THEN Amount
                ELSE 0 
            END AS Credits,
            CASE 
                WHEN TransactionType = 'DEBIT' THEN Amount
                ELSE 0 
            END AS Debits,
            CASE 
                WHEN TransactionType = 'CREDIT' THEN Amount
                ELSE -Amount 
            END AS NetAmount
        FROM AccountTransactions
        WHERE AccountId = @AccountId
          AND TransactionDate BETWEEN @StartDate AND @EndDate
    )
    SELECT 
        t.TransactionId,
        t.TransactionDate,
        t.Description,
        t.Credits,
        t.Debits,
        SUM(t.NetAmount) OVER (ORDER BY t.TransactionDate, t.TransactionId 
                               ROWS UNBOUNDED PRECEDING) AS RunningBalance
    FROM Transactions t
);
 
-- Usage: Generate bank-style statement
SELECT * FROM dbo.GetAccountStatement(12345, '2024-01-01', '2024-01-31')
ORDER BY TransactionDate, TransactionId;

Summary: Mastering Table-Valued Functions

Key Takeaways

•TVFs return result sets — They appear in FROM clauses and can be joined, filtered, and aggregated like tables.
•Inline TVFs are parameterized views — The optimizer fully integrates them into query plans; always prefer them when possible.
•Multi-statement TVFs are procedural black boxes — Use only when loops or complex logic require it; expect performance overhead.
•APPLY enables row-by-row TVF execution — CROSS APPLY and OUTER APPLY correlate TVF calls to outer table rows.
•PostgreSQL uses set-returning functions — Similar concepts with RETURNS TABLE and LATERAL joins providing equivalent functionality.
•Design patterns include parameterized views, string splitting, and hierarchy traversal — These reusable patterns solve common problems efficiently.

Page Complete

3 / 5