Scalar UDFs and Row-by-Row Processing

SQL Server is a set-based engine. It is very good at "do this to ten million rows" and comparatively poor at "do this once, ten million times." Two constructs turn the first into the second.

Scalar user-defined functions

CREATE FUNCTION dbo.fn_FormatCustomerName (@id int)
RETURNS nvarchar(200)
AS
BEGIN
    DECLARE @n nvarchar(200);
    SELECT @n = LastName + ', ' + FirstName FROM dbo.Customer WHERE CustomerId = @id;
    RETURN @n;
END;

Called in a SELECT list over 200,000 rows, on SQL Server 2017 and earlier this executes 200,000 times, each as a separate invocation with its own context switch and its own query against dbo.Customer. Three specific consequences:

No inlining into the plan. The function's internals are invisible — the plan shows a cheap Compute Scalar with a near-zero cost estimate while the function consumes most of the runtime. This is the most misleading thing in an execution plan.
Costing is wrong. The optimizer assigns scalar UDFs a trivial cost, so plans built around them are built on a false premise.
Parallelism is disabled. In older versions, a scalar UDF anywhere in the query forces the whole plan serial. On a report that would otherwise use eight cores, that alone can be an 8x slowdown.

A scalar UDF in a WHERE clause is worse still — non-SARGable by definition, plus the per-row call.

Scalar UDF inlining (SQL Server 2019+)

SQL Server 2019 introduced automatic inlining: qualifying scalar UDFs are transformed into relational expressions folded into the calling query, which removes the per-row invocation and restores parallelism and correct costing. It requires compatibility level 150 or higher, and not every function qualifies — functions with WHILE loops, EXEC, table variables, @@ROWCOUNT or non-deterministic behavior are excluded.

Check whether yours qualifies:

SELECT  OBJECT_SCHEMA_NAME(object_id) AS schema_name,
        OBJECT_NAME(object_id)        AS function_name,
        is_inlineable,                 -- 1 = eligible for 2019+ inlining
        inline_type                    -- 1 = inlining currently enabled for it
FROM sys.sql_modules
WHERE OBJECTPROPERTY(object_id, 'IsScalarFunction') = 1;

SELECT name, compatibility_level FROM sys.databases WHERE name = DB_NAME();

is_inlineable = 0 on a function your report calls per row is a concrete, actionable finding.

The rewrites

Inline table-valued function — the optimizer expands these into the query like a view, so they cost correctly and parallelize:

CREATE FUNCTION dbo.tvf_CustomerName (@id int)
RETURNS TABLE
AS
RETURN (SELECT CAST(LastName + ', ' + FirstName AS nvarchar(200)) AS FullName
        FROM dbo.Customer WHERE CustomerId = @id);
GO

SELECT o.OrderId, n.FullName
FROM   dbo.Orders AS o
CROSS APPLY dbo.tvf_CustomerName(o.CustomerId) AS n;

Note the distinction: inline TVFs (a single RETURN (SELECT ...)) expand into the plan. Multi-statement TVFs (RETURNS @t TABLE ... BEGIN ... END) do not — they materialize into a table variable with a fixed row estimate, and carry many of the same problems as scalar UDFs.

Or simply write the expression in the query, or join instead of calling. Most scalar UDFs in reporting code are a join wearing a function's clothing.

Cursors and WHILE loops

-- The shape to avoid
DECLARE cur CURSOR FOR SELECT OrderId FROM dbo.Orders WHERE StatusId = 1;
OPEN cur;
FETCH NEXT FROM cur INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.Orders SET Processed = 1 WHERE OrderId = @id;
    FETCH NEXT FROM cur INTO @id;
END
CLOSE cur; DEALLOCATE cur;

Every iteration is a full statement: parse, plan lookup, execute, log. A hundred thousand iterations is a hundred thousand of those. The set-based version does the same work in one statement:

UPDATE dbo.Orders SET Processed = 1 WHERE StatusId = 1;

Find cursors in your codebase:

SELECT  OBJECT_SCHEMA_NAME(m.object_id) AS schema_name,
        OBJECT_NAME(m.object_id)        AS object_name,
        o.type_desc
FROM sys.sql_modules AS m
JOIN sys.objects     AS o ON o.object_id = m.object_id
WHERE m.definition LIKE '%DECLARE%CURSOR%'
   OR m.definition LIKE '%WHILE @@FETCH_STATUS%'
ORDER BY schema_name, object_name;

And find scalar UDFs being called from anywhere:

SELECT  OBJECT_SCHEMA_NAME(referencing_id) AS calling_schema,
        OBJECT_NAME(referencing_id)        AS calling_object,
        referenced_schema_name,
        referenced_entity_name
FROM sys.sql_expression_dependencies AS d
WHERE EXISTS (
        SELECT 1 FROM sys.objects AS o
        WHERE o.name = d.referenced_entity_name
          AND o.type = 'FN'                       -- FN = scalar function
      );

When a cursor is legitimate

Not every loop is wrong. A cursor is reasonable when:

You are calling an external process per row (sending an email, invoking a service).
You are deliberately batching a huge modification to keep transactions and log growth bounded — a WHILE loop doing DELETE TOP (5000) until no rows remain is a good pattern, not a bad one.
Administrative code iterating over databases or tables.

The distinction is whether the loop is doing per-row data work that a set-based statement could do, or per-row control work that genuinely has to be sequential.

The same pattern in application code

The C# equivalent is the N+1 query: fetch a list, then loop and query per item. It has exactly the same shape and the same cost, moved to the client with network round-trips added. In EF Core, lazy loading in a foreach produces it silently. In a report, "fetch the rows, then look up a description for each row" produces it too.

Detect it from the database side: an enormous execution_count on a small, fast query is the fingerprint.

SELECT TOP (20)
       qs.execution_count,
       qs.total_elapsed_time / 1000                 AS total_ms,
       qs.total_elapsed_time / qs.execution_count / 1000.0 AS avg_ms,
       SUBSTRING(t.text, 1, 200) AS query_snippet
FROM sys.dm_exec_query_stats AS qs
CROSS APPLY sys.dm_exec_sql_text(qs.sql_handle) AS t
WHERE qs.execution_count > 10000
ORDER BY qs.execution_count DESC;

A query averaging 2 ms and executing 400,000 times in an hour costs the server more than your 40-second report, and it is almost certainly a loop somewhere.

The short version

Scalar UDFs run once per row and, before 2019, force the plan serial and cost as near-zero — so the plan looks cheap while the function is the entire runtime. Check sys.sql_modules.is_inlineable to see whether 2019 inlining applies, and otherwise rewrite to an inline table-valued function or a join. The application-side version of the same problem is N+1, and you find it by looking for a tiny query with a huge execution count.