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.
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
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.