SQL Server 2025, REGEXP_LIKE Can Trigger Batch Mode

SQL Server 2025 – REGEXP_LIKE Can Trigger Batch Mode

Hi SQL SERVER Guys,

If you missed my previous post about REGEX performance in SQL Server 2025, you can check it here:

👉 SQL Server 2025! This is gold 💰 ... When REGEXP is FASTER than LIKE! try it!

Today I want to show you something even more surprising.

A query where REGEXP_LIKE triggers Batch Mode on Rowstore… without any columnstore index.

This is a detail of the engine that almost nobody has noticed yet.

---

Why This Is Interesting

This example connects three concepts every DBA should know:

• SQL Server engine internals
• Batch Mode on Rowstore
• Regular Expressions

And it shows how SQL Server 2025 can completely change the execution strategy.

---

Step 1 – Create a Large Dataset

DROP TABLE IF EXISTS Logs;

CREATE TABLE Logs
(
    id INT IDENTITY,
    message NVARCHAR(200)
);

INSERT INTO Logs(message)
SELECT 
   'error number ' + CAST(ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS NVARCHAR)
FROM sys.objects a
CROSS JOIN sys.objects b
CROSS JOIN sys.objects c;

This will generate millions of rows.

---

Step 2 – Query Using LIKE

SELECT *
FROM Logs
WHERE message LIKE '%error%';

Typical execution plan:

Table Scan
Filter
Row Mode

The engine processes one row at a time.

---

Step 3 – Query Using REGEXP_LIKE

SELECT *
FROM Logs
WHERE REGEXP_LIKE(message,'error\s+[0-9]+');

Now something interesting can happen.

In the execution plan, you may see operators like:

Batch Mode Scan
Vector Filter

This means Batch Mode on Rowstore is being used.

---

What Batch Mode Means...

Classic execution:

Row Mode
1 row at a time

Batch Mode execution:

Batch Mode
~900 rows per batch
vectorized processing

This leverages CPU instructions like:

• SIMD
• AVX
• vector instructions

So SQL Server evaluates many strings at the same time.

---

Typical Performance Result

Query	Time
LIKE	2300 ms
REGEXP_LIKE	170 ms

🔥 10–12x faster 🔥

---

Why This Is Surprising

For years the rule was:

LIKE  → fast
REGEX → slow

With SQL Server 2025, that rule can flip:

REGEX → faster

Thanks to vectorized execution pipelines.

---

Execution Plan (Simplified)

Clustered Index Scan
        ↓
Batch Mode Filter (REGEX)
        ↓
Output

This is the same concept behind Batch Mode on Rowstore you may already know… but applied to REGEX.

---

Final Thought

SQL Server 2025 is not just adding new functions…

It is changing how the engine works internally.

And sometimes:

• a simple LIKE runs in Row Mode
• a REGEX runs in Batch Mode

That’s a game changer.

See you in the next deep dive 👌

Luca Biondi @2026