String concatenation in T-SQL: + vs CONCAT, measured

Gluing strings together is the most ordinary thing T-SQL does, and there are two ways to do it: the + operator we’ve all written for decades, and CONCAT(), which arrived in SQL Server 2012 and still gets treated as the new kid. Which one should you reach for — and does it matter at the row level, where string expressions actually run?

I measured it: expressions evaluated once per row over a 200,000-row scan on SQL Server 2025, reported as nanoseconds per row net of a scan-only baseline. (Why per-row? These expressions cost nanoseconds — far below what batch-level timing can resolve. Loop design, interleaved passes, and min-of-trials estimation are the same as in the methodology post; the scan baseline on this box is ~140 ns/row, and everything below is the expression’s own cost on top.)

Round 1: pure strings, increasing piece count

Concatenating two, four, and eight VARCHAR columns (~20 chars each):

Pieces	a + b + …	CONCAT(a, b, …)
2	34.5 ns/row	36.0 ns/row
4	86.9 ns/row	64.9 ns/row
8	195.6 ns/row	108.8 ns/row

At two pieces it’s a tie — pick either. From four pieces up, CONCAT pulls ahead, and by eight pieces + costs 1.8× more. I’ll admit my pre-registered hypothesis said the opposite (surely the humble operator beats the function call?), and the data disagreed — and the shape of the numbers explains why. + is a binary operator: a + b + c + d is really ((a + b) + c) + d, three operations each producing an intermediate string that exists only to be copied into the next one. CONCAT is a single n-ary call that assembles the result once. More pieces, more intermediates, wider gap — the classic Shlemiel-the-painter pattern, hiding in an expression we all type without thinking.

Round 2: mixed types

To concatenate a number with + you must cast it yourself; CONCAT converts implicitly. People sometimes guess the implicit conversion makes CONCAT slow:

CAST(n1 AS VARCHAR(12)) + s1 + CAST(n2 AS VARCHAR(12))   -- 93.7 ns/row
CONCAT(n1, s1, n2)                                       -- 87.0 ns/row

No — same work, and CONCAT is slightly ahead (it’s still winning on piece count). The query plan shows it’s literally the same conversions; the compiled expression for CONCAT(n1, s1, n2) is:

concat(CONVERT_IMPLICIT(varchar(12),[t].[n1],0),
       [t].[s1],
       CONVERT_IMPLICIT(varchar(12),[t].[n2],0))

You can write the casts or let CONCAT write them — the engine runs the same thing, and your code is shorter.

Round 3: the one that actually matters — NULL

Here’s the round that should decide your default, and it isn’t about speed. Against a column that’s NULL in 50% of rows:

Expression	ns/row	Result when sn50 is NULL
s1 + sn50	24.2	NULL — the whole result is gone
CONCAT(s1, sn50)	26.3	s1 (NULL treated as empty)
s1 + ISNULL(sn50, '')	36.9	s1

Yes, the raw + is the fastest line in the whole experiment — because for half the rows it short-circuits to NULL and destroys your data. Every veteran has debugged this: one nullable column silently nulls out an entire concatenated address, label, or log line. The honest comparison is between the two NULL-safe forms, and there CONCAT beats the ISNULL-guarded + by ~40% — while also being the version you can’t forget to guard. (If you want NULLs handled and separators between non-NULL pieces, CONCAT_WS('|', …) does both for a modest premium: 89.9 vs 64.9 ns/row at four pieces — and it skips NULLs rather than gluing empty strings around your separator.)

Keep the magnitudes honest

These are nanoseconds. The worst gap measured — + vs CONCAT at eight pieces, ~87 ns/row — adds up to 17 milliseconds per million rows. In an ETL job formatting hundreds of millions of rows, that’s real money; in an OLTP query touching 50 rows, it’s nothing. As with the rest of this series: if a query is slow, its concatenation syntax is almost never why.

Verdict

• Default to CONCAT for anything beyond two pieces: it’s never slower in these tests, increasingly faster as pieces grow, handles type conversion for you, and — most importantly — can’t silently NULL your output.
• + is fine for two non-nullable strings, which is most ad-hoc concatenation. Don’t rewrite working code over nanoseconds.
• The moment a nullable column enters the expression, + becomes a correctness trap, and the “fix” (ISNULL wrapping) is slower than just using CONCAT. Semantics decide this one; the performance happens to agree.
• For separator-joined lists, CONCAT_WS — and for aggregating rows into one string, that’s STRING_AGG, a different tool (and a future post).

---

Part of an ongoing series of measured answers to small T-SQL questions: see SET vs SELECT vs DECLARE, temp-table cleanup, ISNULL vs COALESCE, and IIF vs CASE. Harness + raw data reproducible end-to-end against a disposable SQL Server 2025 container — get in touch for the scripts.