REGEXP_LIKE Performance: Why Pattern Filters Cannot Seek

REGEXP_LIKE performance depends on how many rows reach the pattern test, because a pattern filter cannot seek an index. The pattern is short. The CPU bill is not. Narrow the rows first, then let the pattern inspect only what is left.

The validation query that got slow

Say you have a table of order codes and a job that finds the well-formed ones. The code must look like ORD- followed by four digits. REGEXP_LIKE says that in one line, and the query works on day one.

Then the table grows. The job that took a blink now runs for minutes, and nobody touched the query. Let me show why with a demo table of 200,000 codes.

REGEXP_LIKE is new in SQL Server 2025. I ran this demo at database compatibility level 170, so check yours first.

SELECT compatibility_level
FROM sys.databases
WHERE database_id = DB_ID();

Build the codes

Most rows start with INV-, PAY- or SHP-. One in ten starts with ORD-. Half of those are well-formed, like ORD-1234, and half are not, like ORD-X123. An index on Code sits on top.

The last query counts the pieces. You get 200000 rows, 20000 with the ORD- prefix, and 10000 in the exact format. Keep those numbers in mind. The pattern has to sort the good from the bad among 20000 candidates.

DROP TABLE IF EXISTS #Codes;
CREATE TABLE #Codes (CodeId int NOT NULL PRIMARY KEY, Code varchar(40) NOT NULL);

WITH n AS (
    SELECT TOP (200000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS i
    FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b)
INSERT #Codes (CodeId, Code)
SELECT i,
       CASE WHEN i % 20 = 0 THEN 'ORD-' + RIGHT('0000' + CAST(i % 10000 AS varchar(10)), 4)
            WHEN i % 20 = 1 THEN 'ORD-X' + CAST(i AS varchar(10))
            WHEN i % 3 = 0 THEN 'INV-' + CAST(i AS varchar(10))
            WHEN i % 3 = 1 THEN 'PAY-' + CAST(i AS varchar(10))
            ELSE 'SHP-' + CAST(i AS varchar(10)) END
FROM n;

CREATE INDEX IX_Codes_Code ON #Codes (Code);

SELECT COUNT(*) AS total_rows,
       SUM(CASE WHEN Code LIKE 'ORD-%' THEN 1 ELSE 0 END) AS ord_prefix,
       SUM(CASE WHEN Code LIKE 'ORD-[0-9][0-9][0-9][0-9]' THEN 1 ELSE 0 END) AS exact_format
FROM #Codes;

Anchor the pattern

Before speed, correctness. Without the ^ and $ anchors, a regular expression matches anywhere inside the text. The first column below is true for XORD-12345, which is not a valid code. The second column is false, as it should be.

SELECT CASE WHEN REGEXP_LIKE('XORD-12345', 'ORD-[0-9]{4}') THEN 'match' ELSE 'no match' END AS unanchored,
       CASE WHEN REGEXP_LIKE('XORD-12345', '^ORD-[0-9]{4}$') THEN 'match' ELSE 'no match' END AS anchored;

Count the reads

Now the two versions of the query. The first uses the pattern alone. The second adds LIKE ‘ORD-%’, which an index on Code can use as a range. Both return 10000 matches.

Look at the logical reads. In my run, the pattern alone read 583 pages. With the LIKE prefix it read 61. Your numbers will differ, but the gap should look similar.

SET STATISTICS IO ON;

SELECT COUNT(*) AS matches FROM #Codes
WHERE REGEXP_LIKE(Code, '^ORD-[0-9]{4}$');

SELECT COUNT(*) AS matches FROM #Codes
WHERE Code LIKE 'ORD-%' AND REGEXP_LIKE(Code, '^ORD-[0-9]{4}$');

SET STATISTICS IO OFF;

Read the plan

The plan explains the gap. SHOWPLAN_TEXT prints it as plain lines. The first query shows an Index Scan, with the regexp_like call as a filter on every row it reads. The second shows an Index Seek on the ORD- range, and the pattern runs only on rows inside that range.

SET SHOWPLAN_TEXT ON;
GO
SELECT COUNT(*) AS matches FROM #Codes
WHERE REGEXP_LIKE(Code, '^ORD-[0-9]{4}$');
GO
SELECT COUNT(*) AS matches FROM #Codes
WHERE Code LIKE 'ORD-%' AND REGEXP_LIKE(Code, '^ORD-[0-9]{4}$');
GO
SET SHOWPLAN_TEXT OFF;

That is the whole lesson. The index can narrow by ordinary comparisons. It cannot understand a character pattern. So give the index something to seek, and let the pattern do the last mile.

Give the pattern fewer candidates

When a prefix or category is a common filter, store it and index it. A real column or a LIKE prefix gives the optimizer a seek. Do not hope that a general B-tree understands your pattern.

Keep the pattern itself small and anchored too. Complex patterns can cost more per row. One more habit: run your own query with SET STATISTICS TIME ON and watch the CPU time too.

DROP TABLE IF EXISTS #Codes;

Before you blame the pattern, count how many rows reach it.

A pattern filter is not a seek key, it is work done on every candidate row.

Published by Pinal Dave on SQLAuthority. More of my work at pinaldave.com.