SQL Server Interview Question #114

What is an inline table-valued function?

Views, Stored Procedures & User-Defined Functions Mid-Level Intermediate

Detailed Explanation

An inline table-valued function (iTVF) returns a table from a single SELECT expression. It can accept parameters, making it similar to a parameterized view.

Because its relational expression can often be incorporated effectively into the calling query by the optimizer, an inline TVF is generally preferable to a multi-statement TVF when the required logic can be expressed as one relational query.

Code Example

CREATE FUNCTION dbo.GetProductsByCategory
(
    @CategoryId INT
)
RETURNS TABLE
AS
RETURN
(
    SELECT Id, Name, Price
    FROM dbo.Products
    WHERE CategoryId = @CategoryId
);
GO

SELECT *
FROM dbo.GetProductsByCategory(10);