Issue saving Latin characters on SQL Server

Question

I'm having an issue when saving text data to a SQL Server 2019 database using Latin_General_CI_AS as the default collation. The column in question is set as varchar. When the client types a word like não it saves normally with no issue, but if he copy+paste the word it saves as n~ao. Debugging I found out that when copy pasting instead of using the unicode character "ã" it uses a combining diacritical mark "~" plus the letter "a", and looks like varchar can't handle it.

I already saw that changing to nvarchar might solve the issue, but I would need to do it on a lot of columns, is there any other way around this issue other than changing the column data type?

Answer 1

Changing the datatype to nvarchar is one way to solve it.

Another way to solve this is by changing the settings on your database and/or table by using collation like Latin_General_CI_AI for some case-insensitive and accent-insensitive comparisons, which can handle Latin characters well.

Usually nvarchar is used because it has a wider range of Latin characters and other non-ASCII characters than varchar