Can't update "CO2" to "CO₂" in table row
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;