0

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?

Devin Burke
  • 13,642
  • 12
  • 55
  • 82

1 Answers1

1

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".

Max Zolotenko
  • 1,082
  • 7
  • 13
  • As I said, the second line (with `123456`) does *not* receive a truncation error. – Devin Burke Jan 04 '20 at 21:28
  • 1
    Because `replicate('x', 123456)` return `vachar(8000)` - not varchar(max) or varchar(123456). – Max Zolotenko Jan 04 '20 at 21:33
  • select 1 where '' like cast(replicate(cast('x' as varchar(max)), 123456) as varchar(max)) --truncation error – lptr Jan 05 '20 at 08:51
  • @lptr https://learn.microsoft.com/en-us/sql/t-sql/functions/replicate-transact-sql?view=sql-server-ver15 here described a behaviour of the REPLICATE function in such cases: "If string_expression is not of type varchar(max) or nvarchar(max), REPLICATE truncates the return value at 8,000 bytes. To return values greater than 8,000 bytes, string_expression must be explicitly cast to the appropriate large-value data type." So, in case replicate('x', 123456) it returns varchar(8000) in your case it returns varchar(max). – Max Zolotenko Jan 05 '20 at 09:40
  • @Максим Золотенко , true, the comment was added as a clarification to the question of Devin Burke (...(with 123456) does not receive a truncation error.). – lptr Jan 05 '20 at 09:50