Creating index on computed field: string or binary data would be truncated

I have a table Foo with the following fields:

ID bigint not null identity(1,1),
SerializedValue nvarchar(max),
LongValue as TRY_CAST(SerializedValue as bigint)

Now I want to create an index on LongValue, so that I can easily look up on serialized values that represent numbers.

create nonclustered index IX_Foo on Foo(LongValue);

Which spits out the following error at me:


String or binary data would be truncated.

Yes, there is existing data in SerializedValue. But what, pray, can be truncated by creating an index on a computed field?

The error isn't caused by creating the index. The error is caused by TRY_CAST when the computed column values are evaluated at index creation.

If I run this:

SELECT TRY_CAST(REPLICATE(CONVERT(nvarchar(MAX), N'a'), 4001) AS bigint)

I get the same error.

The documentation says (emphasis mine):


If the cast succeeds, TRY_CAST returns the value as the specified data_type; if an error occurs, null is returned. However if you request a conversion that is explicitly not permitted, then TRY_CAST fails with an error.

Now, it's not exactly clear under which cases it will fail with an error (seems kind of asinine given the whole point of the function, but anyway...), so we can fix up the code by transforming the input values (use something reasonable for the data in your table), as there's no need to process a huge string when it won't fit in a bigint anyway:

SELECT TRY_CAST(LEFT(REPLICATE(CONVERT(nvarchar(MAX), N'1'), 4001), 100) AS bigint)

This returns NULL as the value isn't valid, but it doesn't bomb with an error.

StackExchange Database Administrators Q#65245, answer score: 8