Tested on SQL Server 2022 (Developer edition, single test instance), October 2026.

Hardware and settings only go so far. Most slow systems have a few code patterns that stop SQL Server working in sets or using its indexes. These are the ones to look for, with what to use instead.

Row-by-row processing: cursors and WHILE loops

A loop that handles one row at a time does a full statement (and often a full plan execution and log write) per row. The same work as one set-based statement is usually 10 to 100 times faster.

-- Row by row: one UPDATE per order
DECLARE @id int;
DECLARE c CURSOR LOCAL FAST_FORWARD FOR
    SELECT OrderID FROM dbo.Orders WHERE OrderDate < '2026-02-01';
OPEN c;
FETCH NEXT FROM c INTO @id;
WHILE @@FETCH_STATUS = 0
BEGIN
    UPDATE dbo.Orders SET Amount = Amount * 1.1 WHERE OrderID = @id;
    FETCH NEXT FROM c INTO @id;
END;
CLOSE c; DEALLOCATE c;

-- Set based: one statement
UPDATE dbo.Orders SET Amount = Amount * 1.1 WHERE OrderDate < '2026-02-01';

Loops are still right for work that must be done in batches to keep transactions and log growth small (for example deleting millions of rows a few thousand at a time), and for calling a procedure per item. Process a batch of rows per loop, never one row.

-- Batched delete: each loop is a short transaction
DECLARE @rows int = 1;
WHILE @rows > 0
BEGIN
    DELETE TOP (5000) FROM dbo.Orders WHERE OrderDate < '2026-01-15';
    SET @rows = @@ROWCOUNT;
END;

Scalar user-defined functions

A scalar function used in a SELECT or WHERE clause runs once per row, hides its cost from the plan, and (before SQL Server 2019) forces the whole query to run on one core.

CREATE OR ALTER FUNCTION dbo.OrderBand (@Amount money)
RETURNS varchar(10)
AS
BEGIN
    RETURN CASE WHEN @Amount >= 500 THEN 'Large' WHEN @Amount >= 100 THEN 'Medium' ELSE 'Small' END;
END;
GO

-- Can SQL Server 2019+ inline it? (needs database compatibility level 150 or higher)
SELECT OBJECT_NAME(object_id) AS function_name, is_inlineable
FROM sys.sql_modules
WHERE object_id = OBJECT_ID(N'dbo.OrderBand');

SQL Server 2019 can inline simple scalar functions automatically (is_inlineable = 1). Functions that read tables in loops, use GETDATE() or other time-dependent functions, or call other non-inlineable functions are not inlined. The reliable fix is to put the logic in the query, or rewrite the function as an inline table-valued function and use CROSS APPLY:

CREATE OR ALTER FUNCTION dbo.OrderBandTVF (@Amount money)
RETURNS TABLE
AS RETURN
    SELECT CASE WHEN @Amount >= 500 THEN 'Large' WHEN @Amount >= 100 THEN 'Medium' ELSE 'Small' END AS Band;
GO

SELECT o.OrderID, b.Band
FROM dbo.Orders o
CROSS APPLY dbo.OrderBandTVF(o.Amount) b;

Multi-statement table-valued functions

A function that declares a table variable, fills it and returns it is a black box to the optimiser: it is estimated at 1 row (100 rows with the 2014 to 2016 estimator), and SQL Server 2017 onwards only improves this for some plans (interleaved execution). Joins to it are often planned badly. Rewrite as an inline function (a single RETURN SELECT) wherever possible.

Non-SARGable predicates

A predicate is SARGable (Search ARGument able) when SQL Server can use an index seek for it. Wrapping the column in a function, a calculation or a conversion stops that, and the index is scanned instead.

-- Not SARGable: the function is applied to every row
SELECT OrderID FROM dbo.Orders WHERE YEAR(OrderDate) = 2026 AND MONTH(OrderDate) = 3;
SELECT OrderID FROM dbo.Orders WHERE DATEADD(DAY, 30, OrderDate) > SYSDATETIME();
SELECT OrderID FROM dbo.Orders WHERE ISNULL(CustomerID, 0) = 42;

-- SARGable: the column is left alone
SELECT OrderID FROM dbo.Orders WHERE OrderDate >= '2026-03-01' AND OrderDate < '2026-04-01';
SELECT OrderID FROM dbo.Orders WHERE OrderDate > DATEADD(DAY, -30, SYSDATETIME());
SELECT OrderID FROM dbo.Orders WHERE CustomerID = 42;

The same applies to LIKE ‘%text’ (a leading wildcard always scans), and to comparing a column with a value of a different data type, which can add an implicit conversion to the column (a check of the cached plans for CONVERT_IMPLICIT warnings finds these).

Data types

• Use the same data type for a column everywhere it is joined or compared. An nvarchar parameter against a varchar column is the most common cause of implicit conversions.
• Use date for dates and datetime2 for timestamps, not strings.
• Size columns for the data. nvarchar(max) and varchar(max) columns cannot be index key columns and make memory grants larger.

Building strings: STRING_AGG instead of loops

-- Comma-separated order IDs per customer (SQL Server 2017+)
SELECT CustomerID,
       STRING_AGG(CAST(OrderID AS varchar(max)), ',') WITHIN GROUP (ORDER BY OrderID) AS order_ids
FROM dbo.Orders
WHERE OrderDate >= '2026-06-01'
GROUP BY CustomerID;

Cast the value to varchar(max) or nvarchar(max) as shown. Otherwise the result is limited to 8,000 bytes and a customer with many orders fails with error 9829. On SQL Server 2016 and earlier the equivalent is FOR XML PATH(”) with STUFF to remove the first comma.

A numbers table instead of a loop

Many loops exist only to produce a list of numbers or dates. On SQL Server 2022, GENERATE_SERIES does this directly; on earlier versions, keep a permanent numbers table.

-- Every day in March 2026, with its order count, including days with none
SELECT d.day, COUNT(o.OrderID) AS orders
FROM (SELECT DATEADD(DAY, value, '2026-03-01') AS day FROM GENERATE_SERIES(0, 30)) d
LEFT JOIN dbo.Orders o ON o.OrderDate = d.day
GROUP BY d.day
ORDER BY d.day;

-- Earlier versions: build a numbers table once
SELECT TOP (100000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) - 1 AS n
INTO dbo.Numbers
FROM sys.all_objects a CROSS JOIN sys.all_objects b;
CREATE UNIQUE CLUSTERED INDEX CX_Numbers ON dbo.Numbers (n);

Test properly while developing

• Test with realistic data volumes and skew, not ten rows per table.
• Check the actual plan and STATISTICS IO for every new query, not just whether it returns the right answer.
• Watch for SELECT *: it stops covering indexes being used and breaks code when columns change.

From my SQL Server DBA handbook.