Prev: DateTime type in SQL Server 2008
Next: UPDATE into large table
From: althafexcel on 29 Sep 2009 12:47
Hi

I'm using SQL Server 2005 on a .NET project that deals with multiple
languages, During installation i made the SQL collation to default.
But as of now i will be using English & Arabic languages in my
project, how do i set the collation now.

Should i have to change the COLLATION for the whole SQL SERVER 2005 or
for the new database that i will be creating for my project. Please
advice me the steps on how to set the collation.

What about if i include the FRENCH & SPANISH language too in the same
project. I'm very confused, plz. help me out guys...
From: David Portas on 29 Sep 2009 13:57
<althafexcel(a)gmail.com> wrote in message
news:32a8af17-5c8a-49ea-9380-4cc7b212e548(a)h13g2000yqk.googlegroups.com...
> Hi
>
> I'm using SQL Server 2005 on a .NET project that deals with multiple
> languages, During installation i made the SQL collation to default.
> But as of now i will be using English & Arabic languages in my
> project, how do i set the collation now.
>
> Should i have to change the COLLATION for the whole SQL SERVER 2005 or
> for the new database that i will be creating for my project. Please
> advice me the steps on how to set the collation.
>
> What about if i include the FRENCH & SPANISH language too in the same
> project. I'm very confused, plz. help me out guys...

It is the *column* collation rather than the server or database collation
that determines the way data is sorted and compared. Since you are using
multiple languages you will perhaps want to control sorting and comparison
at runtime rather than design time. Use the COLLATE clause in a query for
that. None of the collation settings affects the way data is stored. They
only determine how it is compared and sorted.

--
David Portas


From: BrianR on 1 Oct 2009 07:00
It might be an idea to consider using Unicode data type for all
character column types - use the nvarchar, nchar datatypes rather that
varchar, char. There is a storage overhead when using unicode
(characters take up 2 bytes instead of 1 byte for non-unicode) so you
need to be aware of that. However unicode is good when using multiple
languages in the same database. Also be aware to use the N'text' when
dealing with unicode strings http://support.microsoft.com/default.aspx/kb/239530

If you have separate databases for each language then these databases
can all have different collations, you need to be aware that the
server collation is important in certain cases - especially since the
tempdb will use it. For example , that If you perform joins from your
db with temp tables and the columns have a different collation you can
have some issues. However, as long as devs are aware of collation
differences, you can write joins to be 'collation insensitive', so in
this case its OK to have databases on your server with different
collations

for example - My server collation is Latin1_General_CI_AS

so I create a new database with a different collation as below:
----- Collation test script

CREATE DATABASE [testdb]
COLLATE Arabic_CI_AI

-- create a table in your database

Use [testdb]

create table db1
(id int,
somechardata varchar(10)
)

--create a table in tempdb (# means its a temp table)
create table #tmptable
(id int,
somechardata varchar(10)
)

--insert some data

insert db1 values(1,'text1')
insert db1 values(2,'text1')
insert db1 values(3,'text3')

insert #tmptable values(1,'text1')
insert #tmptable values(2,'text2')

-- now try a query with a join to the temp table

select * from db1 join #tmptable t
on db1.somechardata = t.somechardata

--you'll find this fails with the message
--Msg 468, Level 16, State 9, Line 3
--Cannot resolve the collation conflict between "Latin1_General_CI_AS"
and "Arabic_CI_AI" in the equal to operation.

--now run the same query, but explicily tell sql server to use an
appropriate collation

select * from db1 join #tmptable t
on db1.somechardata COLLATE Arabic_CI_AI = t.somechardata COLLATE
Arabic_CI_AI

--This time it works

----- End of Collation test script

Alternatively, when creating character columns in tempdb, explitly use
the collation of your database such as:

--create a table in tempdb (# means its a temp table)
create table #tmptable
(id int,
somechardata varchar(10) COLLATE Arabic_CI_AI
)

Youll need to test different scenarios, I normally leave the server
collation as the 'default' when you install, which is based on the
servers ['Regional and Language Options' | Advanced | Language for non
unicode programs] setting in the control panels. In my case its
Latin1_General_CI_AS.

 | 
Pages: 1
Prev: DateTime type in SQL Server 2008
Next: UPDATE into large table