Clustered index key length warning on varchar(120)?

Question

So today i have been doing some optimization creating some indexed views etc. and I came across this warning

Warning! The maximum key length for a clustered index is 900 bytes. The index 'IX_...' has maximum length of 8004 bytes. For some combination of large values, the insert/update operation will fail.

The index is

CREATE UNIQUE CLUSTERED INDEX IX_.. ON  [aView] ([id], [type])

The view is

CREATE VIEW aView
WITH SCHEMABINDING
AS 
SELECT Id, Replace(Replace([aField],'....',''),'....','') AS [Type], COUNT_BIG(*) AS DistinctTotal
FROM .....
INNER JOIN........

Id is an INT

aField in the physical table is a VARCHAR(120)

So the maximum key length for the index would be 120+4 bytes would it not?

Why am i seeing this warning?

Is it valid?

Answer 1

For your specific situation, analyzing all inputs to your REPLACE calls, we can see that they can only ever leave the string the same length or smaller.

But in general, REPLACE can in theory return a varchar(8000) even if one of its inputs was only a varchar(120). SQL Server doesn't perform a complex analysis here - it sees the replace and assumes a return type of varchar(8000).

Since we know it will never exceed 120 characters after all replacements, add an explicit CONVERT(varchar(120),<existing replaces>) wrapper around the expression.