CODEWITHSUNDEEP
sqlsql-serversql-likenvarcharvarcharmax
Devin Burke
Devin Burke

Reputation: 13820

"String or binary data would be truncated." for NVARCHAR but not VARCHAR in LIKE operation

In SQL Server, nvarchar takes twice the space of varchar, and its pre-page-pointer limit is 4000 compared to varchar's 8000.

So, why does the following like comparison give a String or binary data would be truncated. error...

select 1 where '' like cast(replicate('x', 4001) as nvarchar(max))

...while casting as a massively larger varchar does not?

select 1 where '' like cast(replicate('x', 123456) as varchar(max))

In fact, why does the top live give a truncation error at all when it's clearly declared as nvarchar(max) which has a size limit of about 2GB?

Upvotes: 0

Views: 1155

Answers (1)

Max Zolotenko
Max Zolotenko

Reputation: 1132

From the description of the LIKE operator:

pattern

Is the specific string of characters to search for in match_expression, and can include the following valid wildcard characters. pattern can be a maximum of 8,000 bytes.

This query shows a factual count of symbols:

select  len(replicate('x', 123456)) as  CntVarchar,
        len(replicate('x', 4001))   as  CntNVarchar


+------------+-------------+
| CntVarchar | CntNVarchar |
+------------+-------------+
|       8000 |        4001 |
+------------+-------------+

The first case has 8000 bytes. The second has 8002 bytes, that violates the rule "can be a maximum of 8,000 bytes".

Upvotes: 1

Related Questions