Tired of changing collations!
I was looking at writing a blog post on changing collations, so the first thing I did was to restore a backup of Adventureworks to give myself something I could test on.
User-defined functions and views created with SCHEMABINDING.
Table-valued functions that return tables with character columns with collations inherited from the default database collation.
As you may know changing the database collation is not a simple matter and you have to cascade the change through the columns of each table yourself.
I have answered countless newsgroup posts about how to change the collation and you can easily search online to get a script of how to change each table, but the problem is not that simple. For instance if you have to change a table collation, you will first have to drop any indexes, statistics, foreign keys, primary keys that reference a collated datatype but that is not all!
If you look in Books Online for the ALTER DATABASE topic you will see other items that may cause the ALTER DATABASE to fail such as:
I noticed there was already a connect item saying that CHECK constraints on non-collated datatypes should not stop you altering the collation for the database:
I created the following script to show this:
CREATE DATABASE dbcollate COLLATE Latin1_General_CI_AS
CREATE TABLE dbo.tbl_collate ( id char(10) not null CONSTRAINT PK_tbl_collate PRIMARY KEY,
numcol int not null CONSTRAINT CK_numcol CHECK (numcol > 0),
compcol AS numcol + 10
Then when you try to change the collation:
ALTER DATABASE dbcollate COLLATE SQL_Latin1_General_CP1_CI_AS
You get the following messages:
Msg 5075, Level 16, State 1, Line 1
The column 'tbl_collate.compcol' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5075, Level 16, State 1, Line 1The object 'CK_numcol' is dependent on database collation. The database collation cannot be changed if a schema-bound object depends on it. Remove the dependencies on the database collation and then retry the operation.
Msg 5072, Level 16, State 1, Line 1
ALTER DATABASE failed. The default collation of database 'dbcollate' cannot be set to SQL_Latin1_General_CP1_CI_AS.
So why do computed columns and check constraints on non-collated datatypes stoping you from changing the collation?
The reason could be that computed columns and check contraints are stored in sys.syscolumns as text, but then stored procedures and triggers are not an issue!
SELECT OBJECT_NAME(id) AS [Object], colid, text
WHERE OBJECT_NAME(id) IN ( 'tbl_collate', 'CK_numcol' )
A change from a case sensitivity and non-case sensitive collations can mean that code held as text in syscomments could fail. In that scenario invalid constraints and defaults would impact the structure of the database.
Erland Sommarskog has posted a request to make changing collations easier, if like me you think this problem has lingered too long then go and vote for it on connect: