Scalar Functions — Reusable Calculations and Formatting

Scalar user-defined functions (UDFs) return a single value and can be called anywhere an expression is valid — in SELECT lists, WHERE clauses, and computed column definitions. They are ideal for encapsulating reusable business calculations: reading time from word count, generating a URL slug from a title, masking sensitive data. However, scalar UDFs in WHERE clauses have a critical performance flaw that every developer must understand before using them in production queries.

Scalar Functions #

-- ── Slug generator — title to URL-safe string ─────────────────────────────
CREATE OR ALTER FUNCTION dbo.fn_GenerateSlug
    (@Title NVARCHAR(200))
RETURNS VARCHAR(200)
WITH SCHEMABINDING    -- prevents dropping referenced tables/columns
AS
BEGIN
    DECLARE @Slug VARCHAR(200);

    -- Lowercase, replace spaces with hyphens
    SET @Slug = LOWER(@Title);
    SET @Slug = REPLACE(@Slug, ' ', '-');

    -- Remove non-alphanumeric characters except hyphens
    -- (simplified — full implementation would use a loop or CLR function)
    SET @Slug = REPLACE(@Slug, '''', '');
    SET @Slug = REPLACE(@Slug, '.', '');
    SET @Slug = REPLACE(@Slug, ',', '');
    SET @Slug = REPLACE(@Slug, '!', '');
    SET @Slug = REPLACE(@Slug, '?', '');
    SET @Slug = REPLACE(@Slug, ':', '');
    SET @Slug = REPLACE(@Slug, '--', '-');  -- collapse double hyphens
    SET @Slug = LTRIM(RTRIM(REPLACE(@Slug, '-', ' ')));  -- trim edge hyphens
    SET @Slug = REPLACE(@Slug, ' ', '-');

    -- Truncate to 200 chars
    RETURN LEFT(@Slug, 200);
END;
GO

-- ── Reading time calculator ────────────────────────────────────────────────
CREATE OR ALTER FUNCTION dbo.fn_ReadingTimeMinutes
    (@Body NVARCHAR(MAX))
RETURNS INT
WITH SCHEMABINDING
AS
BEGIN
    -- Average reading speed: 200 words per minute
    DECLARE @WordCount INT;
    SET @WordCount = LEN(TRIM(@Body))
        - LEN(REPLACE(TRIM(@Body), ' ', '')) + 1;
    RETURN CEILING(@WordCount / 200.0);
END;
GO

-- ── Email masker for admin displays ───────────────────────────────────────
CREATE OR ALTER FUNCTION dbo.fn_MaskEmail
    (@Email NVARCHAR(256))
RETURNS NVARCHAR(256)
AS
BEGIN
    DECLARE @At  INT = CHARINDEX('@', @Email);
    IF @At <= 1 RETURN @Email;

    DECLARE @Local  NVARCHAR(128) = LEFT(@Email, @At - 1);
    DECLARE @Domain NVARCHAR(128) = RIGHT(@Email, LEN(@Email) - @At);

    -- Show first 2 chars + *** + domain
    RETURN LEFT(@Local, 2) + '***@' + @Domain;
END;
GO

-- ── Using scalar functions ─────────────────────────────────────────────────
-- ✅ In SELECT — acceptable performance
SELECT
    p.Title,
    dbo.fn_GenerateSlug(p.Title)          AS GeneratedSlug,
    dbo.fn_ReadingTimeMinutes(p.Body)     AS ReadingTime,
    dbo.fn_MaskEmail(u.Email)             AS MaskedEmail
FROM dbo.Posts p
JOIN dbo.Users u ON u.Id = p.AuthorId;

-- ⚠️  In WHERE — AVOID: executes once per row, prevents index use
-- Slow: WHERE dbo.fn_ReadingTimeMinutes(p.Body) > 5
-- Fast: use a persisted computed column instead (covered in Chapter 67)

Common Mistakes #

Mistake 1 — Scalar UDF in WHERE clause (disables indexes, row-by-row execution) #

❌ Wrong — WHERE dbo.fn_Category(p.CategoryId) = 'Tech'; scans all rows calling the function each time.

✅ Correct — materialise the computed value in a column with an index, or restructure the query to avoid the function in WHERE.

Mistake 2 — Missing SCHEMABINDING on production functions #

❌ Wrong — function without SCHEMABINDING; a developer drops or renames a referenced column; function breaks silently at runtime.

✅ Correct — add WITH SCHEMABINDING to all functions that reference database objects.