I had a recent run in with collation problems and it got me started reading about them. As I read I started to realize that for a seemingly simple subject there was a lot to it. So I thought I would go over some of what I found out.

You’ve installed your instance, created your database, created your tables even started running your queries. And then you realize that at some level or another you’ve made a mistake and now you need to change your collation. This gets more complicated the farther up the chain you go. So starting at the bottom:

Expression

At the expression level collation is easy to change. I went into this in a fair amount of detail in Collation: Expression but basically you just use the COLLATE clause to modify the collation of any string, be it column or variable.

Column

As with any change to a column definition you have to remove any check constraints, indexes, keys etc before you can make the change. Once everything is out of the way you can change the collation of the column by using the ALTER TABLE ALTER COLUMN command. Remember of course that this changes the entire definition of the column so make sure when you write your ALTER command that you know the actual definition of the column.

CREATE TABLE Collation_Test (
Col1 char(10) COLLATE Latin1_General_CI_AI_WS NOT NULL 
CONSTRAINT pk_Collation_Test PRIMARY KEY ,
Col2 char(10) COLLATE Latin1_General_CI_AI_WS NULL 
CONSTRAINT ck_Collation_Test CHECK (Col2 = 'x'),
Col3 char(10) COLLATE Latin1_General_CI_AI_WS NOT NULL
)
GO
CREATE INDEX ix_Collation_Test ON Collation_Test(Col3)
GO

ALTER TABLE Collation_Test ALTER COLUMN Col1 char(10) 
COLLATE Latin1_General_BIN NOT NULL 
GO
ALTER TABLE Collation_Test ALTER COLUMN Col2 char(10) 
COLLATE Latin1_General_BIN NULL 
GO
ALTER TABLE Collation_Test ALTER COLUMN Col3 char(10) 
COLLATE Latin1_General_BIN NOT NULL 
GO

Because of the primary key, constraint and index you will see the following errors

Msg 5074, Level 16, State 1, Line 1

The object ‘pk_Collation_Test’ is dependent on column ‘Col1′.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN Col1 failed because one or more objects access this column.

Msg 5074, Level 16, State 1, Line 1

The object ‘ck_Collation_Test’ is dependent on column ‘Col2′.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN Col2 failed because one or more objects access this column.

Msg 5074, Level 16, State 1, Line 1

The index ‘ix_Collation_Test’ is dependent on column ‘Col3′.

Msg 4922, Level 16, State 9, Line 1

ALTER TABLE ALTER COLUMN Col3 failed because one or more objects access this column.

So in order to make it work you have to remove them, make the change, and re-add them.

ALTER TABLE Collation_Test DROP CONSTRAINT pk_Collation_Test
ALTER TABLE Collation_Test ALTER COLUMN Col1 char(10) 
COLLATE Latin1_General_BIN NOT NULL 
ALTER TABLE Collation_Test 
ADD CONSTRAINT pk_Collation_Test PRIMARY KEY (Col1)
GO
ALTER TABLE Collation_Test DROP CONSTRAINT ck_Collation_Test
ALTER TABLE Collation_Test ALTER COLUMN Col2 char(10) 
COLLATE Latin1_General_BIN NULL 
ALTER TABLE Collation_Test 
ADD CONSTRAINT ck_Collation_Test CHECK (Col2 = 'x')
GO
DROP INDEX Collation_Test.ix_Collation_Test
ALTER TABLE Collation_Test ALTER COLUMN Col3 char(10) 
COLLATE Latin1_General_BIN NOT NULL 
CREATE INDEX ix_Collation_Test ON Collation_Test(Col3)
GO

Database

Altering the collation on an empty database is fairly easy using an ALTER DATABASE command.

ALTER DATABASE DatabaseName COLLATE Latin1_General_BIN

Unfortunately databases are rarely empty by the time you realize you have the wrong collation. To start with there are a handful of objects that will block the ALTER DATABASE just like an index or constraint does a ALTER TABLE.

• User defined functions and views created with SCHEMABINDING
• Computed Columns
• Check constraints
• Table-valued functions that return tables with character columns with collations inherited from the default database collation.

And in addition the collation change can’t cause there to be duplicate object names. So for example if the database has a case sensitive collation and you have created objects “Table” and “table” then you cannot change to a non case sensitive collation because this will cause those object names to be duplicates.

This means that in order to do the alter you first have to script out and drop all “problem” objects and deal with any potential duplicate object names. Then you make your change and put back the objects you scripted out. Unfortunately that doesn’t end things.

Changing the database collation does not affect any existing columns created with a different collation. So once you have changed your database collation you still have to go back and change column.

Last time I did this I came up with 3 different methods for handling the problem.

1. For a small database (defined as only a few tables of whatever size) with simple tables you can just do it the long way. Change your database collation then each of the column collations using the various ALTER statements
2. For a small database with more complicated tables (multiple constraints, indexes etc) I would alter the database then use the table design GUI to change each of the tables.
3. And last but certainly not least I would use the generate scripts wizard.
   1. Go into the scripting options and turn all of the scripting options on.
   2. Go to the generate scripts wizard and generate a script for all objects in the database.
   3. Search and replace all occurrences in the script of the old collation with the new one
   4. Create a new database with the correct collation
   5. Run your script on the new database to create all of the objects needed
   6. Transfer any data
   7. Use a rename swap to change the old database to another name and the new one to the correct DB Name

That last option is generally the easiest but can still be a big pain particularly if you have a lot of data.

Over a number of different posts I’m going to discuss some of the surprisingly deep subject of collation.

As a start listen to Rob Farley’s hilarious talk on collation
Collation: Definition
Collation: Location
Collation: Expression
Collation: Correction -> Instance
Collation: Temporary objects & variables: Demonstration
Collation: Confusion

Filed under: Collation, Microsoft SQL Server, SQLServerPedia Syndication, T-SQL Tagged: code language, collation, language sql, microsoft sql server, T-SQL