This post is a part of the DP-800: Developing AI-Enabled Database Solutions Exam Prep Hub.
This topic falls under these sections:
Design and develop database solutions (35–40%)
   --> Implement programmability objects
      --> Create table-valued functions

Note that there are 10 practice questions (with answers) at the end of each section to help you solidify your knowledge of the material. Also, there are 4 practice tests with 30 questions each available from the hub's main page below the exam topics section.

Introduction

Table-valued functions (TVFs) are user-defined database objects in SQL Server and Azure SQL Database that return a table rather than a single scalar value. They enable developers to encapsulate reusable query logic that can be invoked like a table within SQL statements.

Unlike scalar functions, which return a single value, table-valued functions return a result set that can be filtered, joined, aggregated, and queried just like a regular table or view. They are widely used to simplify complex queries, implement reusable business logic, parameterize data retrieval, and support reporting, analytics, and AI-enabled database solutions.

For the DP-800: Developing AI-Enabled Database Solutions certification exam, you should understand:

• What table-valued functions are
• Inline table-valued functions (iTVFs)
• Multi-statement table-valued functions (MSTVFs)
• How to create, modify, and delete TVFs
• Parameters and return tables
• Performance differences between iTVFs and MSTVFs
• Appropriate use cases
• Best practices

Understanding TVFs is important because they provide reusable, parameterized query logic while integrating seamlessly into T-SQL queries.

What Is a Table-Valued Function?

A table-valued function is a user-defined function that returns a table.

Unlike stored procedures, TVFs can be referenced directly in the FROM clause of a query.

Example:

SELECT *
FROM dbo.fnActiveCustomers();

The returned table behaves like any other table expression.

Benefits of Table-Valed Functions

TVFs provide numerous advantages:

• Encapsulate reusable query logic
• Accept input parameters
• Return structured result sets
• Simplify complex SQL
• Improve maintainability
• Support modular application design
• Can be joined with other tables
• Can be filtered and aggregated

Types of Table-Valued Functions

SQL Server supports two types:

1. Inline Table-Valued Functions (iTVFs)
2. Multi-Statement Table-Valued Functions (MSTVFs)

Understanding the differences is important for the DP-800 exam.

Inline Table-Valued Functions (iTVFs)

An inline TVF consists of a single SELECT statement.

General syntax:

CREATE FUNCTION dbo.FunctionName
(
    @Parameter DataType
)
RETURNS TABLE
AS
RETURN
(
    SELECT ...
);

Example:

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

Usage:

SELECT *
FROM dbo.fnOrdersByCustomer(100);

Characteristics of Inline TVFs

Inline TVFs:

• Return a single SELECT statement
• Do not declare table variables
• Are generally optimized like parameterized views
• Typically offer the best performance
• Can participate fully in query optimization

For most scenarios, Microsoft recommends using inline TVFs whenever possible.

Multi-Statement Table-Valued Functions (MSTVFs)

A multi-statement TVF allows multiple T-SQL statements.

Unlike inline TVFs, it declares and populates a table variable.

Example:

CREATE FUNCTION dbo.fnLargeOrders
(
    @MinimumAmount MONEY
)
RETURNS @Orders TABLE
(
    OrderID INT,
    CustomerID INT,
    TotalAmount MONEY
)
AS
BEGIN
    INSERT INTO @Orders
    SELECT
        OrderID,
        CustomerID,
        TotalAmount
    FROM Sales.Orders
    WHERE TotalAmount >= @MinimumAmount;
    RETURN;
END;

Characteristics of Multi-Statement TVFs

MSTVFs:

• Support multiple SQL statements
• Allow procedural logic
• Can declare variables
• Can perform multiple INSERT operations into the return table
• Are generally slower than inline TVFs because the optimizer has less information about the returned data

Comparing iTVFs and MSTVFs

Using Parameters

TVFs commonly accept parameters.

Example:

SELECT *
FROM dbo.fnOrdersByCustomer(25);

Parameters make TVFs reusable across different queries.

Joining a TVF with Tables

Because TVFs return tables, they can participate in joins.

Example:

SELECT
    c.CustomerName,
    o.OrderID,
    o.TotalAmount
FROM dbo.fnOrdersByCustomer(100) AS o
INNER JOIN Sales.Customers AS c
    ON o.CustomerID = c.CustomerID;

Using CROSS APPLY

TVFs are frequently used with CROSS APPLY.

Example:

SELECT
    c.CustomerID,
    o.OrderID,
    o.TotalAmount
FROM Sales.Customers AS c
CROSS APPLY dbo.fnOrdersByCustomer(c.CustomerID) AS o;

CROSS APPLY invokes the function once for each row returned by the outer query.

This is one of the most common uses of TVFs.

Using OUTER APPLY

OUTER APPLY behaves similarly to a left outer join.

Example:

SELECT
    c.CustomerID,
    o.OrderID
FROM Sales.Customers AS c
OUTER APPLY dbo.fnOrdersByCustomer(c.CustomerID) AS o;

Customers without matching orders still appear in the result set, with NULL values for the function’s columns.

Modifying a TVF

Use ALTER FUNCTION.

Example:

ALTER FUNCTION dbo.fnOrdersByCustomer
(
    @CustomerID INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT
        OrderID,
        OrderDate,
        TotalAmount,
        Status
    FROM Sales.Orders
    WHERE CustomerID = @CustomerID
);

Dropping a TVF

Example:

DROP FUNCTION dbo.fnOrdersByCustomer;

Viewing a Function Definition

Developers can inspect the function definition using:

sp_helptext 'dbo.fnOrdersByCustomer';

Or:

SELECT OBJECT_DEFINITION(OBJECT_ID('dbo.fnOrdersByCustomer'));

Schema Binding

TVFs can be created using WITH SCHEMABINDING.

Benefits include:

• Prevents incompatible schema changes
• Improves object stability
• Can be required for certain database features

Deterministic vs. Nondeterministic Functions

A TVF may be:

Deterministic

• Same inputs produce the same outputs.

Nondeterministic

• Results may change between executions.
• Examples include functions using GETDATE() or NEWID().

Deterministic functions are generally preferred for predictable behavior and optimization.

TVFs vs. Views

One of the biggest advantages of a TVF over a view is its ability to accept parameters.

TVFs vs. Stored Procedures

TVFs vs. Scalar Functions

Performance Considerations

Performance is an important exam topic.

Inline TVFs

• Usually have excellent performance.
• The optimizer expands them into the calling query.
• Can benefit from accurate cardinality estimation.
• Often perform similarly to parameterized views.

Multi-Statement TVFs

• Use a table variable internally.
• Historically provided limited cardinality estimates, which could result in less efficient execution plans.
• May perform more slowly on large result sets.

Whenever possible, use an inline TVF unless procedural logic requires a multi-statement implementation.

AI-Enabled Database Scenarios

TVFs are useful in AI-enabled database solutions because they provide reusable, parameterized datasets.

Examples include:

• Returning embeddings associated with a specific document or tenant
• Filtering vectors by category before similarity searches
• Returning AI-ready feature sets for model inference
• Preparing Retrieval-Augmented Generation (RAG) context based on user or document parameters
• Returning standardized datasets for prompt construction
• Producing reusable search result sets for intelligent search

Parameterized TVFs help AI applications retrieve only the data relevant to a specific request.

Best Practices

• Prefer inline TVFs whenever possible.
• Keep functions focused on a single responsibility.
• Use meaningful names such as fnOrdersByCustomer.
• Avoid unnecessary procedural logic.
• Return only the columns required.
• Keep functions deterministic whenever practical.
• Test performance with realistic workloads.
• Use CROSS APPLY or OUTER APPLY appropriately.
• Document business logic within the function.
• Review execution plans when TVFs are used in critical queries.

Common Exam Tips

For the DP-800 exam, remember these key points:

• Table-valued functions return a table.
• TVFs can accept parameters.
• Inline TVFs contain a single SELECT statement.
• Multi-statement TVFs populate a table variable using multiple statements.
• Inline TVFs generally outperform multi-statement TVFs.
• TVFs can be used in the FROM clause.
• TVFs can participate in JOIN, CROSS APPLY, and OUTER APPLY operations.
• Views cannot accept parameters, but TVFs can.
• ALTER FUNCTION modifies an existing TVF.
• DROP FUNCTION removes a TVF.

Practice Exam Questions

Question 1

A developer needs a reusable database object that accepts parameters and returns a result set that can be queried like a table. Which object should be used?

A. Stored procedure

B. Table-valued function

C. Scalar function

D. Trigger

Answer: B

Explanation: A table-valued function returns a table and can be referenced in the FROM clause while accepting input parameters.

Question 2

Which statement correctly describes an inline table-valued function?

A. It populates a table variable using multiple INSERT statements.

B. It returns exactly one scalar value.

C. It consists of a single SELECT statement that defines the returned table.

D. It cannot accept parameters.

Answer: C

Explanation: Inline TVFs are defined by a single SELECT statement and generally provide better performance because they integrate well with the query optimizer.

Question 3

What is one primary advantage of a table-valued function over a view?

A. It always performs faster.

B. It can automatically create indexes.

C. It can accept input parameters.

D. It can modify database schema.

Answer: C

Explanation: Unlike views, table-valued functions support input parameters, making them reusable for parameterized queries.

Question 4

A developer needs to use procedural logic and multiple INSERT statements to build a returned result set. Which type of function should be created?

A. Inline table-valued function

B. Scalar function

C. View

D. Multi-statement table-valued function

Answer: D

Explanation: Multi-statement TVFs allow multiple T-SQL statements and populate a table variable before returning it.

Question 5

Which operator is commonly used to invoke a table-valued function for each row returned by an outer query?

A. UNION

B. CROSS APPLY

C. EXISTS

D. PIVOT

Answer: B

Explanation: CROSS APPLY executes the TVF for each row in the outer query, making it ideal for parameterized row-by-row processing.

Question 6

Why do inline table-valued functions generally outperform multi-statement table-valued functions?

A. They automatically create clustered indexes.

B. They execute in parallel regardless of the query.

C. They are optimized similarly to parameterized views, allowing better query optimization.

D. They always return fewer rows.

Answer: C

Explanation: The SQL Server optimizer can expand inline TVFs into the calling query, producing more efficient execution plans than are typically possible with multi-statement TVFs.

Question 7

Which statement about table-valued functions is correct?

A. They can only return one column.

B. They cannot participate in JOIN operations.

C. They cannot accept parameters.

D. They can be queried in the FROM clause just like a table.

Answer: D

Explanation: TVFs return a table and can be used in the FROM clause, joined with other tables, and filtered like regular tables.

Question 8

Which statement should be used to modify an existing table-valued function?

A. UPDATE FUNCTION

B. MODIFY FUNCTION

C. CREATE FUNCTION

D. ALTER FUNCTION

Answer: D

Explanation: ALTER FUNCTION changes the definition of an existing table-valued function while preserving the object.

Question 9

A developer wants all customers returned, even if a table-valued function produces no matching rows for some customers. Which operator should be used?

A. INNER JOIN

B. CROSS APPLY

C. OUTER APPLY

D. INTERSECT

Answer: C

Explanation: OUTER APPLY behaves similarly to a left outer join, returning all rows from the outer query and NULL values when the TVF produces no matching rows.

Question 10

How can table-valued functions support AI-enabled database solutions?

A. They automatically train machine learning models.

B. They replace vector indexes.

C. They provide reusable, parameterized datasets that simplify intelligent search, feature retrieval, and Retrieval-Augmented Generation (RAG) workflows.

D. They eliminate the need for indexes.

Answer: C

Explanation: TVFs encapsulate reusable, parameterized query logic, making them well suited for AI scenarios such as feature retrieval, intelligent search, and RAG, where filtered and consistent datasets are required.

Go to the DP-800 Exam Prep Hub main page