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?

Please try creating an simple example ,so that we can test it — TheGameiswar, Sep 28 '17 at 09:00
https://learn.microsoft.com/en-us/sql/t-sql/data-types/int-bigint-smallint-and-tinyint-transact-sql INT is 4 bytes and as stated type is < 120 bytes — Mark, Sep 28 '17 at 09:46

Damien_The_Unbeliever · Accepted Answer · 2017-09-28T09:38:30.783

3

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.

edited Sep 28 '17 at 09:38

answered Sep 28 '17 at 08:58

Damien_The_Unbeliever

234,701
27
340
448

Thanks, I had a sneaking suspicion that's what was happening but couldn't easily find an answer so asked the SO :) – Mark Sep 28 '17 at 09:49

Clustered index key length warning on varchar(120)?

1 Answers1