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

Question

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?

Answer 1

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.

Answer 2

If you have a string with a value that is too long, then the index create will fail. I tried a little test code using SQL Server 2012.

CREATE TABLE dbo.foo 
(ID bigint not null identity(1,1),
SerializedValue nvarchar(max),
LongValue as TRY_CAST(SerializedValue as bigint));

INSERT INTO dbo.foo (serializedvalue) VALUES(REPLICATE(' ', 4000)+'1');

CREATE INDEX GotToTry ON foo(LongValue);

DROP TABLE dbo.foo;
GO

My quick experiment showed that the code works as long as the nvarchar(max) value is 4000 characters or less. (Of course, all blanks with nothing on the end collapses to no characters and thus works just fine.) The 4001st character triggers the String or binary data would be truncated message. So you might examine your data for a SerializedValue that is longer than 4000 characters.

EDIT: Yes, the conversion is to a BIGINT. The problem is not the BIGINT, but is the NVARCHAR(MAX). For example:

1. If a row contains '1111111111111111111' it will both CREATE INDEX and convert the value to BIGINT.
2. If a row is 0 to 4000 '1's, it can CREATE INDEX, but the value may be NULL since it overflows BIGINT.
3. If a row is longer than 4000 characters, then the CREATE INDEX fails.

So, it seems that the actual contents of the NVARCHAR(MAX) is what matters to the CREATE INDEX.

EDIT: Jon Seigel identified that the TRY_CAST triggers the failure on create index when the string is longer than nvarchar(4000).