Table-Valued Functions — SQL Functions | OneNoughtOne

0/278

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.

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.

Table-Valued Functions

Functions That Return Tables

Table-Valued Function Fundamentals

Inline Table-Valued Functions

Multi-Statement Table-Valued Functions

APPLY Operators with TVFs

PostgreSQL Set-Returning Functions

TVF Design Patterns

Performance Optimization

There’s more to understand.

Table-Valued Functions

Functions That Return Tables

Table-Valued Function Fundamentals

Inline Table-Valued Functions

Multi-Statement Table-Valued Functions

APPLY Operators with TVFs

PostgreSQL Set-Returning Functions

TVF Design Patterns

Performance Optimization

There’s more to understand.