Changing column collation

Hi all,
I have an interesting doubt:
In our SQL environments we have some problems with different collations.
Unfortunatelly when this servers and db's were created people didn't care
about collations.
Now would like normalize these environments and db's.
I have a DB with SQL collation SQL_Latin1_General_CP1_CI_AS and all tables
with string fields Latin1_General_CI_AS.
I did an scripts to first drop index and constraints of these string fields,
after I created a script with ALTER TABLE ALTER COLUMN to change the field
collation.
I would expected that this command would do the conversion of the data, but
it was so fast that I'm almost sure that it change only the collation in the
system table...
If i try to change the collation in Enterprise Manager and ask for save the
script SQL always create a new table and transfer the data doing so the
conversion.
My doubt is: If the command ALTER COLUMN doesn't convert the data, the SQL
collation SQL_Latin1_General_CP1_CI_AS is compatible with windows collation
Latin1_General_CI_AS in the sense that also there's no conversion I will not
have any join (between char fieds) problems?
Regards and thanks a lot
Alexandre Calderaro
MCDBA Avanade ItalyHi Alex
You didn't post the script you ran to change the tables. If your data in the
columns are mainly in the normal range (of alphanumerics) there should not b
e
a need to change the data. The following post gives a way of creating a
script to change the collations http://tinyurl.com/qwulo this assumes the
database has alredy been chagned to the correct collation with an ALTER
DATABASE statement.
John
"Alex" wrote:

> Hi all,
> I have an interesting doubt:
> In our SQL environments we have some problems with different collations.
> Unfortunatelly when this servers and db's were created people didn't care
> about collations.
> Now would like normalize these environments and db's.
> I have a DB with SQL collation SQL_Latin1_General_CP1_CI_AS and all tables
> with string fields Latin1_General_CI_AS.
> I did an scripts to first drop index and constraints of these string field
s,
> after I created a script with ALTER TABLE ALTER COLUMN to change the field
> collation.
> I would expected that this command would do the conversion of the data, bu
t
> it was so fast that I'm almost sure that it change only the collation in t
he
> system table...
> If i try to change the collation in Enterprise Manager and ask for save th
e
> script SQL always create a new table and transfer the data doing so the
> conversion.
> My doubt is: If the command ALTER COLUMN doesn't convert the data, the SQL
> collation SQL_Latin1_General_CP1_CI_AS is compatible with windows collati
on
> Latin1_General_CI_AS in the sense that also there's no conversion I will n
ot
> have any join (between char fieds) problems?
> Regards and thanks a lot
> Alexandre Calderaro
> MCDBA Avanade Italy|||Thanks John,
I have a similar script to change the collation of all tables.
For each column I have a similar script:
ALTER TABLE tab ALTER COLUMN col nvarchar(255) COLLATE
SQL_Latin1_General_CP1_CI_AS NOT NULL
Could using a Unicode datatype change the behavior of this command?
My doubt is: what's the difference between using ALTER COLUMN (very fast)
and create a new table with the new collation and transfer data using INSERT
SELECT (obviously much more resource consuming and it's that SQL does if you
change the collation using Enterprise Manager)?
Regards
Alexandre
"John Bell" wrote:
[vbcol=seagreen]
> Hi Alex
> You didn't post the script you ran to change the tables. If your data in t
he
> columns are mainly in the normal range (of alphanumerics) there should not
be
> a need to change the data. The following post gives a way of creating a
> script to change the collations http://tinyurl.com/qwulo this assumes the
> database has alredy been chagned to the correct collation with an ALTER
> DATABASE statement.
> John
>
> "Alex" wrote:
>|||Hi
Enterprise Manager tends to use a belt and braces approach to the SQL
generated and not always the most efficient. The alter table command does no
t
have to move the data around. Your alter table command should be fine, but i
t
is always better to thoroughly test in a non-production first!
John
"Alex" wrote:
[vbcol=seagreen]
> Thanks John,
> I have a similar script to change the collation of all tables.
> For each column I have a similar script:
> ALTER TABLE tab ALTER COLUMN col nvarchar(255) COLLATE
> SQL_Latin1_General_CP1_CI_AS NOT NULL
> Could using a Unicode datatype change the behavior of this command?
> My doubt is: what's the difference between using ALTER COLUMN (very fast)
> and create a new table with the new collation and transfer data using INSE
RT
> SELECT (obviously much more resource consuming and it's that SQL does if y
ou
> change the collation using Enterprise Manager)?
> Regards
> Alexandre
> "John Bell" wrote:
>