Why does TRY_CAST throw an error when attempting to convert VARCHAR to UNIQUEIDENTIFIER?

Question

I have a VARCHAR(MAX) column in a table that can store data in many different formats, including the GUID format. I tried the following code when joining a UNIQUEIDENTIFIER column in another table:

TRY_CAST(b.val AS uniqueidentifier) = a.guid_col

I get the following error:

Msg 8152, Level 16, State 10, Line 73

String or binary data would be truncated.

The documentation for TRY_CAST does say that errors can be thrown in some circumstances:

However if you request a conversion that is explicitly not permitted, then TRY_CAST fails with an error.

However, a conversion from VARCHAR to UNIQUEIDENTIFIER is allowed by SQL Server:

I was able to find an example value that throws the error. The value is quite long so I've included in it a fiddle. Helpful commenters provided a simpler reproduction of the issue:

DECLARE @s VARCHAR(MAX) = REPLICATE(CAST('A' AS VARCHAR(MAX)), 8001);

SELECT TRY_CAST(@s AS UNIQUEIDENTIFIER);

Why does TRY_CAST throw an error instead of returning a NULL value when attempting to convert certain VARCHAR values to UNIQUEIDENTIFIER?

Answer 1

The conversion chart in the documentation doesn't distinguish between varchar and varchar(max), though another section of the CAST and 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 bigint or date.

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:

• Creating index on computed field: string or binary data would be truncated
• String or binary data would be truncated: Error not caught by TRY_CAST