Table-Valued Functions — Inline TVFs and Multi-Statement TVFs

Table-Valued Functions (TVFs) return a table (a result set of rows) rather than a single scalar value. They bridge the gap between stored procedures (which can return multiple result sets but cannot be composed in queries) and views (which cannot take parameters). An inline TVF (iTVF) is a single SELECT statement — the query optimizer can inline it into the calling query and apply indexes. A multi-statement TVF (msTVF) builds a table variable through multiple statements — more flexible but prevents some optimisations. Always prefer iTVF when possible.

Inline and Multi-Statement TVFs #

-- ── Inline Table-Valued Function — single SELECT, optimizer can inline ──────
-- Usage: SELECT * FROM dbo.fn_GetPostsByTag('dotnet', 1, 10)
CREATE OR ALTER FUNCTION dbo.fn_GetPostsByTag
    (@TagSlug VARCHAR(50), @Page INT = 1, @PageSize INT = 10)
RETURNS TABLE          -- no AS BEGIN...END — just RETURN SELECT
WITH SCHEMABINDING
AS
RETURN (
    SELECT
        p.Id, p.Title, p.Slug, p.Excerpt,
        p.ViewCount, p.PublishedAt,
        u.DisplayName AS AuthorName,
        COUNT(*) OVER() AS TotalCount
    FROM   dbo.Posts p
    JOIN   dbo.Users u  ON u.Id   = p.AuthorId
    JOIN   dbo.PostTags pt ON pt.PostId = p.Id
    JOIN   dbo.Tags     t  ON t.Id      = pt.TagId
    WHERE  p.IsPublished = 1
      AND  t.Slug = @TagSlug
    ORDER  BY p.PublishedAt DESC, p.Id DESC
    OFFSET (@Page - 1) * @PageSize ROWS
    FETCH  NEXT @PageSize ROWS ONLY
);
GO

-- ── Usage: iTVF in a SELECT query (works like a view with parameters) ─────
SELECT * FROM dbo.fn_GetPostsByTag('dotnet', 1, 10);

-- ── CROSS APPLY — apply a TVF to each row ────────────────────────────────
-- Get the top 2 posts for each category
SELECT c.Name AS Category, top2.*
FROM   dbo.Categories c
CROSS  APPLY (
    SELECT TOP 2 p.Title, p.ViewCount
    FROM   dbo.Posts p
    WHERE  p.CategoryId = c.Id AND p.IsPublished = 1
    ORDER  BY p.ViewCount DESC
) AS top2;

-- OUTER APPLY — like LEFT JOIN with a TVF (includes categories with no posts)
SELECT c.Name, latest.*
FROM   dbo.Categories c
OUTER  APPLY (
    SELECT TOP 1 p.Title, p.PublishedAt
    FROM   dbo.Posts p
    WHERE  p.CategoryId = c.Id AND p.IsPublished = 1
    ORDER  BY p.PublishedAt DESC
) AS latest;

-- ── Multi-Statement TVF — for complex procedural logic ────────────────────
-- Use only when inline TVF is insufficient (complex branching, multiple INSERT stages)
CREATE OR ALTER FUNCTION dbo.fn_GetRelatedPosts
    (@PostId INT, @MaxResults INT = 5)
RETURNS @Results TABLE (
    PostId       INT NOT NULL,
    Title        NVARCHAR(200) NOT NULL,
    SharedTags   INT NOT NULL,
    ViewCount    INT NOT NULL
)
AS
BEGIN
    -- Insert posts sharing tags
    INSERT INTO @Results (PostId, Title, SharedTags, ViewCount)
    SELECT TOP (@MaxResults)
        p.Id, p.Title, COUNT(DISTINCT pt.TagId) AS SharedTags, p.ViewCount
    FROM   dbo.PostTags pt
    JOIN   dbo.Posts    p ON p.Id = pt.PostId
    WHERE  pt.TagId IN (
               SELECT TagId FROM dbo.PostTags WHERE PostId = @PostId
           )
      AND  p.Id <> @PostId
      AND  p.IsPublished = 1
    GROUP  BY p.Id, p.Title, p.ViewCount
    ORDER  BY SharedTags DESC, p.ViewCount DESC;

    -- If fewer than MaxResults found, add recent posts as fallback
    IF (SELECT COUNT(*) FROM @Results) < @MaxResults
    BEGIN
        INSERT INTO @Results (PostId, Title, SharedTags, ViewCount)
        SELECT TOP (@MaxResults - (SELECT COUNT(*) FROM @Results))
            p.Id, p.Title, 0, p.ViewCount
        FROM   dbo.Posts p
        WHERE  p.IsPublished = 1
          AND  p.Id <> @PostId
          AND  p.Id NOT IN (SELECT PostId FROM @Results)
        ORDER  BY p.PublishedAt DESC;
    END

    RETURN;
END;

Common Mistakes #

Mistake 1 — Using msTVF when an iTVF would work (prevents optimizer push-down) #

❌ Wrong — complex multi-statement TVF for what could be a single parametrised SELECT; worse execution plan.

✅ Correct — always try inline TVF first; only use multi-statement TVF when multiple statements are genuinely required.

Mistake 2 — CROSS APPLY when OUTER APPLY needed (missing rows for empty categories) #

❌ Wrong — CROSS APPLY with category-post TVF; categories with no posts are excluded.

✅ Correct — OUTER APPLY to keep categories with no posts (NULLs for post columns).