Changing Collations

Changing Collations

You can change the collation of a column by using the ALTER TABLE statement:

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 cannot alter the collation of a column that is currently referenced by:

• A computed column.
  
  
• An index.
  
  
• Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
  
  
• A CHECK constraint.
  
  
• A FOREIGN KEY constraint.

You can also use the COLLATE clause on an ALTER DATABASE to change the default collation of the database:

ALTER DATABASE MyDatabase COLLATE French_CI_AS

Altering the default collation of a database does not change the collations of the columns in any existing user-defined tables. These can be changed with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:

• The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.
  
  
• Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.
  
  
• All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.
  
  
• The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.

After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object. This can be a complex operation. To change the default collation for an instance of Microsoft® SQL Server™ 2000 you must:

• Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.
  
  
• Export all of your data using a tool such as bulk copy.
  
  
• Drop all of the user databases.
  
  
• Rebuild the master database specifying the new collation.
  
  
• Create all of the databases and all of the objects in them.
  
  
• Import all of your data.

  Note  Instead of changing the default collation of an instance of SQL Server 2000, you can specify a default collation for each new database you create.