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?
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 avarchar(8000)
even if one of its inputs was only avarchar(120)
. SQL Server doesn't perform a complex analysis here - it sees thereplace
and assumes a return type ofvarchar(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.