Index on calculated column triggers 900 byte index size limit

sql-server, sql-server-2005

Solution

Try `CAST(CASE ... END AS VARCHAR(106))`...

CAST(CASE
    WHEN city_id IS NULL
    THEN name
    ELSE name + REPLICATE(' ', 100 - LEN(name)) + city_id
END AS VARCHAR(106)) COLLATE Modern_Spanish_CI_AI

or simply ignore it... It's only a warning.

Problem

I have a table with a calculated VARCHAR column that will contain up to 106 characters: ``` CREATE TABLE report ( report_id INT IDENTITY(1, 1) NOT NULL, name VARCHAR(100) COLLATE Modern_Spanish_CI_AI NOT NULL, city_id VARCHAR(6) COLLATE Modern_Spanish_CI_AI, unique_name AS CASE WHEN city_id IS NULL THEN name ELSE name + REPLICATE(' ', 100 - LEN(name)) + city_id END COLLATE Modern_Spanish_CI_AI, CONSTRAINT report_pk PRIMARY KEY (report_id) ); /* Report name is unique per city (and among city-less rows) */ CREATE UNIQUE INDEX report_idx1 ON report (unique_name); ``` But when I run the statement I get this warning: Warning! The maximum key length is 900 bytes. The index 'report_idx1' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail. Is there a way to tell SQL Server that the column will not go beyond 106 characters so I get rid of the warning?

Original source