Can't update "CO2" to "CO₂" in table row Given this table:

CREATE TABLE test (
    id INT NOT NULL,
    description NVARCHAR(100) COLLATE Modern_Spanish_CI_AS NOT NULL
);
INSERT INTO test (id, description) VALUES (1, 'CO2');


I've realised I can't fix a typographic issue:

SELECT * FROM test WHERE id = 1;
UPDATE test SET description = 'CO₂' WHERE id = 1;
SELECT * FROM test WHERE id = 1;


because the update matches but has no effect:

id          description
----------- -----------
1           CO2

(1 affected rows)

(1 affected rows)

id          description
----------- -----------
1           CO2

(1 affected rows)


It's as if SQL Server determines that, since ₂ is obviously just a tiny 2, the final value won't change so it's not worth changing it.

Could someone shed some light on this and maybe suggest a workaround (other than updating to an intermediary value)?
The subscript 2 is not part of the varchar character set (in any collation, not just Modern_Spanish). So make it a nvarchar constant:

UPDATE test SET description = N'CO₂' WHERE id = 1;