CODEWITHSUNDEEP
sql-serversql-server-2008-r2collation
dimmope
dimmope

Reputation: 11

Change column collation in sql server 2008 r2, Turkish letters

We will change collation of our existing databases and columns. We use two different collation Turkish_CI_AS,SQL_Latin1_General_CP1254_CS_AS and we will use SQL_Latin1_General_CP1_CI_AS.

I'm wondering if the Turkish special letters are supported by the new collation? So, will the letters like ç,ı,ğ or ü change into different characters?

Upvotes: 0

Views: 1496

Answers (1)

Remus Rusanu
Remus Rusanu

Reputation: 294177

Collations are a different thing from character encoding. Changing the collation only changes the comparison rules of how characters sort, not how they are represented in the database. However, the collation of the data is used as a hint by the client drivers to map the non-unicode characters into specific code pages, according to the table from Collation and Code Page Architecture.

So if your columns are of type VARCHAR or CHAR then the data will (apparently) change, because the characters formerly in the CP 1254 will be interpreted as characters in the CP 850. So ç (\231) will become þ (\231) and so on. Strictly speaking you can reinterpret the data in the client to any code page you desire, but practically that is unfeasible and requires a lot of low level (ie. driver) knowledge.

But if your columns are NVARCHAR or NCHAR then the encoding does not change, since the characters will be UCS-2LE encoded and manipulated as such (ç \u00e7 will remain ç \u00e7). The only thing that will change will be the comparison and sorting rules (which has impact on the famous Turkish collation dotted and dotless I/i comparison rules).

Upvotes: 1

Related Questions