Index of calculated trigger columns 900 bytes

Question

I have a table with a computed 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:

Attention! The maximum key length is 900 bytes. The index 'report_idx1' has a 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 contain more than 106 characters, so I will get rid of the warning?

Answer 1

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 just ignore it ... This is just a warning.