The conversion chart in the documentation doesn't distinguish between
varchar(max), though another section of the
CONVERT documentation says:
Large-value data types
Large-value data types have the same implicit and explicit conversion behavior as their smaller counterparts - specifically, the nvarchar, varbinary, and varchar data types. However, consider the following guidelines:
- Conversion from large-value data types, such as varchar(max), to a smaller counterpart data type, such as varchar, is an implicit conversion, but truncation occurs if the size of the large value exceeds the specified length of the smaller data type.
Also relevant, from char and varchar:
When character expressions are converted to a character data type of a different size, values that are too long for the new data type are truncated. The uniqueidentifier type is considered a character type for the purposes of conversion from a character expression, and so is subject to the truncation rules for converting to a character type.
When you provide a
varchar(max) to the
TRY_* functions with a non-max type as the target, an attempt is made to convert the large value to a non-max string. If that succeeds, all good. If not, you get a "String or binary data would be truncated." error because the max string wouldn't fit. This runs counter to the documentation above, which states that truncation occurs.
My own experience is that
max data types do sometimes have behaviour that doesn't follow the conversion chart or detailed documentation.
This behaviour isn't specific to
uniqueidentifier, the demo in the question will fail with other types like
My recollection is that
TRY_* will work reliably for types compatible with
sql_variant (which excludes large types). You can also use the try functions to convert among max types, just not always from max to non-max.
It's not ideal from a user experience perspective. I don't know if it is a documentation error or unintended behaviour.
As a workaround, you can convert the LOB text to the length of the string representation of a GUID before attempting the try conversion:
DECLARE @s varchar(max) = REPLICATE(CONVERT(varchar(max), 'A'), 8001);
-- No error
SELECT TRY_CONVERT(uniqueidentifier, CONVERT(char(36), @s));
Existing Microsoft feedback item.
Related Q & A: