Database Journal
MS SQL Oracle DB2 Access MySQL PostgreSQL Sybase PHP SQL Etc SQL Scripts & Samples Links Database Forum

» Database Journal Home
» Database Articles
» Database Tutorials
MS SQL
Oracle
DB2
MS Access
MySQL
» RESOURCES
Database Tools
SQL Scripts & Samples
Links
» Database Forum
» Sitemap
Free Newsletters:
DatabaseDaily  
News Via RSS Feed


follow us on Twitter
Database Journal |DBA Support |SQLCourse |SQLCourse2
 

Featured Database Articles

MS SQL

Posted Mar 5, 2003

Beware of Mixing Collations: Part 2- Converting Collations

By Gregory A. Larsen

With SQL Server 2000 you are able to create databases or columns with a different collating setting than the server (discussed in Part 1). This is especially useful when you are trying to restore from a SQL Server 7.0 database backup, where you are not sure of the collating sequence for the SQL Server 7.0 machine. If after your restore you desire to change the collation sequence, then SQL Server 2000 will allow this. Which collation settings you want to change will determine the method, and how much effort it will take to pull off the collation change. This article will explain a couple of methods I have used to successfully change collation settings.

There are three distinct collation settings. The first one is the server collation setting that is initially set when you install SQL Server. There is the database collation setting, which defaults to the server collation setting, or can be specifically defined to be different than the server collation setting when creating a database. Lastly, there are collation settings for columns, that by default take the database collation setting, or if you wish, you can specify a collation setting when creating a column.

Changing the collation setting for a server is quite complex, so I will save that for the end. Although saying that doesn't mean that changing the collation setting for a database and all the character columns in the database is a piece of cake either. Let's first look at how you might change the collation setting for a given column.

Changing a Columns Collation Setting

To alter the collation setting for a given column you use the ALTER TABLE statement. Columns that are referenced in a foreign key, check constraint, index, and/or have distributed statistic cannot be altered without first dropping the column constraints.

Let's go through a couple of examples. The first example will change the collation of a varchar column from Danish_Norwegian_CS_AS to Latin1_General_CI_AS. This example uses the "ALTER COLUMN" clause of the "ALTER TABLE" statement to make the column collation conversion.

CREATE TABLE Test_tbl
  (Record_id   int PRIMARY KEY,
   Name     varchar(50) COLLATE Danish_Norwegian_CS_AS   NOT NULL
  )
GO
select * from information_schema.columns
go
--
-- Here is where the collation setting is changed
--
ALTER TABLE Test_tbl ALTER COLUMN Name
            varchar(50) COLLATE Latin1_General_CI_AS NOT NULL
GO
select * from information_schema.columns

If you run the above code, then review the output displayed from the information_schema.columns view, you can see that the collation setting for the column NAME was changed from Danish_Norwegian_CS_AS to Latin1_General_CI_AS.

Now let's modify the above example slightly, by making the NAME column in table Test_tbl the primary key. By doing this you now will not be able to alter the NAME columns collation setting without first dropping the primary key. As you can see in the example below, I have altered the table to drop the primary key constraint, then altered the column collation, and finally re-created the dropped primary key constraint.

CREATE TABLE Test_tbl
  (Record_id   int ,
   Name     varchar(50) COLLATE Danish_Norwegian_CS_AS   NOT NULL 
constraint pk_name primary key 
  )
GO
select * from information_schema.columns where table_name = 'Test_tbl'
go
--
-- Drop the primary key constraint
--
alter table Test_tbl Drop constraint pk_name
--
-- Here is where the collation setting is changed
--
ALTER TABLE test_tbl ALTER COLUMN Name
	varchar(50)COLLATE Latin1_General_CI_AS NOT NULL
--
-- Re-create the primary key constraint
--
alter table Test_tbl add constraint pk_name primary key (name)
GO
select * from information_schema.columns where table_name = 'Test_tbl'

drop table test_TBL

Now imagine a database that contained a large number of tables and a fair number of constraints (foreign key, check, and index). The process of identifying all of the constraints, then building the script to drop the constraints, altering each character column collation and the re-creating the constraints sounds like a lot of work, right? Well it is! It can be done, but it will be a time consuming, labor-intensive process. When using this method you might be in for a few small bumps in the road, as you try to ALTER COLUMNS that are still referenced by constraints.

I have found this process to be too frustrating for most cases, except when I only have a few columns that need their collation settings changed. If you have a large number of character fields then you might want to read the section below, on migrating a database and columns to a new database with new collation settings.

Changing the Database Collation Setting

Each database has a default collation setting. For each new character field created, that does not have a COLLATE clause, this default setting is used. If you desire to change the default collation setting for a database, you will need to use the ALTER DATABASE command. Here is an example that sets the default collations of the SAMPLE_DB database to French_CI_AS collation:

  
ALTER DATABASE SAMPLE_DB COLLATE French_CI_AS

Be aware that changing the default collation of a database does not change the collation settings of any columns in existing tables. The ALTER COLUMN clause of the alter table statement will need to be used to alter the collation settings for any existing columns in user-defined tables, as described above.



MS SQL Archives

Comment and Contribute

 


(Maximum characters: 1200). You have characters left.

 

 




Latest Forum Threads
MS SQL Forum
Topic By Replies Updated
SQL 2005: SSIS: Error using SQL Server credentials poverty 3 August 17th, 07:43 AM
Need help changing table contents nkawtg 1 August 17th, 03:02 AM
SQL Server Memory confifuration bhosalenarayan 2 August 14th, 05:33 AM
SQL Server Primary Key and a Unique Key katty.jonh 2 July 25th, 10:36 AM