Tired of changing collations!

Published 08 September 09 08:25 PM | MartinBell

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.

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:

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

    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:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=384157&wa=wsignin1.0

    I created the following script to show this:

    CREATE DATABASE dbcollate COLLATE Latin1_General_CI_AS
    GO

    USE
    dbcollate
    GO

    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
    )
    GO

    Then when you try to change the collation:

    USE master
    GO

    ALTER
    DATABASE dbcollate COLLATE SQL_Latin1_General_CP1_CI_AS
    GO

    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!

    USE dbcollate
    GO

    SELECT
    OBJECT_NAME(id) AS [Object], colid, text
    FROM sys.syscomments
    WHERE OBJECT_NAME(id) IN ( 'tbl_collate', 'CK_numcol' )
    GO
     

    Object  Colid Text
    CK_numcol 1 ([numcol]>(0))
    tbl_collate 1 ([numcol]+(10))

    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:

    https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=358320

  • Filed under:

    Comments

    # Dew Drop – September 9, 2009 | Alvin Ashcraft's Morning Dew said on September 9, 2009 01:31 PM:

    Pingback from  Dew Drop – September 9, 2009 | Alvin Ashcraft's Morning Dew

    This Blog

    SQL Blogs

    Syndication