Presents your 
SQL SERVER E-NEWSLETTER for March 27, 2003

<------------------------------------------->

CHANGE COLLATION OF COLUMNS AND DATABASES

A collation is the bit patterns that represent individual characters in
a language character set. A collation also includes the rules used to
sort and compare the characters.

Typically, collation is set during SQL Server installation. On those
computers using a Windows operating system, the collation defaults to the
code page selected in the drop-down box in the Regional Settings of the
Control Panel.

SQL Server 2000 allows you to use different collations within the same
database and even the same table by changing the collation of a column
using the ALTER TABLE statement.

In the example below, we'll create a table called MyTable that uses the
French collation for a column called CharCol. Next we'll change CharCol's
collation to Latin1:

CREATE TABLE MyTable
     (PrimaryKey int PRIMARY KEY,
     CharCol varchar(10) COLLATE French_CI_AS NOT NULL
     )
     Go

ALTER TABLE MyTable ALTER COLUMN CharCol
     varchar(10) COLLATE Latin1_General_CI_AS NOT NULL
Go

You can't change the collation on columns that are being referenced by a
computed column, an index, a CHECK constraint, or a FOREIGN KEY constraint.

To change the collation of the database, use the ALTER DATABASE
statement. This will change the default collation for the database and 
is applied to all columns, variables, and parameters subsequently created
in the database:

ALTER DATABASE MyDatabase COLLATE FRENCH_CI_AS

Changing the collation of a database does not change the collations of
columns in any existing user-defined tables. You have to use the ALTER
TABLE statement to do that.

----------------------------------------