Inline Table-Valued Functions in SQL Server: Parameterized Views That Actually Perform

An inline TVF (iTVF) is essentially a parameterized view — a single SELECT statement wrapped in a function, returning a table. Unlike scalar functions, these get inlined into the calling query’s execution plan, which is why this lesson comes with none of the previous one’s performance warnings.

Writing and Using One

CREATE FUNCTION dbo.GetEmployeesByDepartment (@dept NVARCHAR(50))
RETURNS TABLE
AS
RETURN (
    SELECT employee_id, first_name, last_name, salary
    FROM dbo.Employee
    WHERE department = @dept
);
GO

SELECT * FROM dbo.GetEmployeesByDepartment('Engineering');

-- Can be joined just like a table
SELECT e.first_name, e.salary
FROM dbo.GetEmployeesByDepartment('Sales') e
WHERE e.salary > 60000;

-- Can even be CROSS APPLY'd per row of another table (covered fully in Chapter 6)
SELECT d.department_name, top_earner.first_name
FROM dbo.Department d
CROSS APPLY (SELECT TOP 1 * FROM dbo.GetEmployeesByDepartment(d.department_name) ORDER BY salary DESC) top_earner;

That last example is worth pausing on: an iTVF can take a value from the outer query as its parameter, once combined with CROSS APPLY — something a plain view can never do, since a view has no parameters at all. This is one of the most useful, distinctly-SQL-Server patterns in the whole language.

Why iTVFs Perform Well

Because SQL Server can see straight into an iTVF’s single SELECT, it substitutes the function call with the equivalent SQL directly — conceptually similar to how a compiler inlines a small function — and produces accurate row estimates, exactly as if you’d hand-written the JOIN yourself. No separate execution step, no performance penalty, no black-box row-count guessing.

Proving the Inlining, Not Just Trusting It

-- Compare the execution plan of the function call...
SELECT * FROM dbo.GetEmployeesByDepartment('Engineering') WHERE salary > 70000;

-- ...against the hand-written equivalent. In SSMS, enable "Include Actual Execution Plan"
-- (Ctrl+M) for both and compare — for a true iTVF, the plans are identical.
SELECT employee_id, first_name, last_name, salary
FROM dbo.Employee WHERE department = 'Engineering' AND salary > 70000;

The One Real Limitation

An iTVF’s body must be exactly one SELECT statement — no variables, no IF/ELSE, no intermediate steps. The moment your logic needs a second statement (populate a temp result, then filter it based on something computed in step one), you’ve outgrown an iTVF and need either a multi-statement TVF (next lesson) or a restructured single query using a CTE, which often accomplishes the same goal without leaving iTVF territory at all.