i have to change collation of a database from latin to spanish.

Question

i have to change collation of a database from latin to spanish.

rajesh yadav 191

hi,

1) for spanish we have selected Modern_Spanish_100_ ( i think it is correct)?

kindly suggest
yours sincerley

5 answers

Your answer

Answer 1

Olaf Helper 47,441

Hello,

the collation on database level is only a default one for new objects. The effective collation is defined on column level.
One way is to alter to collation for each column, which will fail, if there is an index on the column; you would have to re-create indexes as well.
Other way is to create a new database will all table/objects with the right collation for char columns and copy over data.

No matter which way you go, it will be a lot of work for you.

rajesh yadav 191 Reputation points

2022-09-27T10:53:54.01+00:00

my question was different , I asked that "Modern_Spanish_100_" is the correct collation for spanish or we have any other collation for that
Olaf Helper 47,441 Reputation points

2022-09-27T11:04:14.897+00:00
my question was different ,

No, it wasn't, you deleted/modified parts of your origin question.
Run

SELECT * FROM sys.fn_helpcollations() as col where col.description like '%spanish%'

On first hand Modern_Spanish_100_ sounds good.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2022-09-27T20:52:38.693+00:00

Again, you need to ask the stakeholders. But I can possibly elaborate a little bit on the topic. You may have noticed that there are both Traditional_Spanish and Modern_Spanish.

For a long time, the digraphs ch and ll counted as separate letters in the Spanish alphabet. That is, chicos would sort after cuando and llave would sort after leche. If this sounds funny to you, I can tell that there are several other languages that also have digraph letters in their alphabet.

However, in 1994, the Royal Spanish Academy said "this is too difficult for the computer age", and decided that ch would now sort within c like it does in English. And likewise does 'll' now sort within l.

So the older way is Traditional_Spanish and the post-1994 rules are Modern_Spanish. I am not enough familiar with the Spanish community to say which is right or wrong in practice. I have a recollection of reading a list of what is the default collation when you install SQL Server, and it was Modern_Spanish_CI_AS for all Spanish locales but one, Spanish (Mexico). But I did not go back and check that list for this post. Because, as I said, you need to talk to the people who are actually going to use your system.

Answer 2

Erland Sommarskog 121.8K MVP Volunteer Moderator

You can change the collation with

   ALTER TABLE tbl ALTER COLUMN col datatype(len) COLLATE Modern_Spanish_100_CI_AS [NOT] NULL

As my template shows, you need to include the exact data type for the column. Also, the command will fail if there is an index on the column, or it is referred to with by a CHECK or FOREIGN KEY constraint.

Or in another words, the change you are looking at is quite painful and not fun at all.

It may be better to create a script for the database, and then do a find/replace of the collation, and then copy over data, table by table. (And hope that constraints don't break, which can happen.)

2) for spanish we have selected Modern_Spanish_100_ ( i think it is correct)?

You will need to ask whoever is in charge about that. We cannot say what is right or wrong for a system we know nothing about.

rajesh yadav 191 Reputation points

2022-09-28T04:26:21.07+00:00

thank u,
on more concern i have used utf8 ,becuase i wanted to use Unicode(multilingual) so i have used Modern_Spanish_100_CI_AS_SC_UTF8
in this I have used SC also I think this combination will have all Unicode range? as my target is to have full range of Unicode.
Olaf Helper 47,441 Reputation points

2022-09-28T08:59:19.853+00:00

i have used utf8 ,becuase i wanted to use Unicode(multilingual)

UTF-8 <> Unicode
And the collation effect ASCII = data type VARCHAR, not NVARCHAR.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2022-09-28T21:13:13.86+00:00
Yes, if you use a UTF8 collation, you can use the full set of Unicode with varchar.

SC stands for Supplementary Characters or Surrogate Compatible. With an SC collation, you have access all characters in the Unicode standard, or more precisely the version of the Unicode standard that the _100_ collations were based on.

The older collations, those without _SC, only understands the Unicode base plane. Here is an example:

CREATE TABLE demo (a nvarchar(100) COLLATE Finnish_Swedish_CS_AS, b nvarchar(100) COLLATE Finnish_Swedish_100_CS_AS) INSERT demo (a, b) VALUES (CAST (0x00D830DF00D831DF00D832DF00D833DF00D834DF3DD815DE AS nvarchar(100)), CAST (0x00D830DF00D831DF00D832DF00D833DF00D834DF3DD815DE AS nvarchar(100))) go SELECT a, datalength(a), len(a) FROM demo

`(I'm using hexstrings where, because the forum UI does not accept characters beyond the base plane.)

Recall that datalength returns the length in bytes, while len return the length in characters (excluding trailing blanks). You can see that for column a, len return the wrong number of characters, because a non-SC collation does not understand the surrogate pairs.
Erland Sommarskog 121.8K Reputation points MVP Volunteer Moderator

2022-09-28T21:19:23.41+00:00

UTF-8 <> Unicode

I'm not sure what you want to say by that. True, they are not the same thing. Unicode is a standard that assign code points for characters. UTF-8 is one of several encoding schemes for Unicode. bu from this follows that UTF-8 implies Unicode.

And the collation effect ASCII = data type VARCHAR, not NVARCHAR.

Even less I understand what you are trying to say here. There is no collation that is constrained to ASCII only for varchar, but each collation supports a code page of at least 200 characters or so for varchar. Collations for East Asian languages supports a whole lot more characters. And the UTF-8 collations supports the full Unicode set (save for the most recent characters).

Answer 3

Hi @rajesh yadav ,
According to this official article (https://learn.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-ver16), the old version column won't be changed for your changing language action. And I think that varchar(100) is only connected with your inner storage. So I will recommed recreating.
Best regards
Jong

If the answer is helpful, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment"

Answer 4

PandaPan-MSFT 1,931

Hi @rajesh yadav ,

Yes.I'm agree with you that "Modern_Spanish_100_" is right. Here is the reference link: https://www.red-gate.com/simple-talk/databases/sql-server/t-sql-programming-sql-server/questions-sql-server-collations-shy-ask/

rajesh yadav 191 Reputation points

2022-09-28T04:26:06.6+00:00

thank u,
on more concern i have used utf8 ,becuase i wanted to use Unicode(multilingual) so i have used Modern_Spanish_100_CI_AS_SC_UTF8
in this I have used SC also I think this combination will have all Unicode range? as my target is to have full range of Unicode.

Answer 5

rajesh yadav 191

thank u,
on more concern i have used utf8 ,becuase i wanted to use Unicode(multilingual) so i have used Modern_Spanish_100_CI_AS_SC_UTF8
in this I have used SC also I think this combination will have all Unicode range? as my target is to have full range of Unicode in spanish language.

Share via

i have to change collation of a database from latin to spanish.

5 answers

Your answer