Azure SQL Server and Collations

This post is more of a warning to other people than anything particularly ground breaking. Please, please, please check the collation you're using when you create a new database on SQL Azure.

Choosing the wrong one can be a major annoyance and a source of subtle bugs. On one of our databases we have a default collation of 'Latin1_General_CP1_CI_AS' but it was migrated from a server with a collation of 'Latin1_General_CI_AS' - so most of the columns in the tables had that collation. This may not seem like much of a problem but we have had a few bugs due to it:

1. Entity Framework - collation conflicts (not sure if still an issue), silent failures...
2. Foreign key problems when adding new tables etc.

In order to make sure we don't have any conflicting collations lying around I wrote a simple script:

select      '[' + t.name + '].[' + c.name + ']' as ColumnName,
             c.collation_name as Collation_Name
from         sys.columns c
inner join   sys.tables t 
        on   c.object_id = t.object_id
where        c.collation_name is not null
        and  c.collation_name <> 'latin1_general_ci_as'

Which gives a list of the errant columns in the database:

It's easy enough to change the collation on the column:

alter table dbo.[TABLE_NAME] alter column
    [COLUMN_NAME] [nvarchar](XXX) collate Latin1_General_CI_AS  [null/not null]
go

Whenever we create a new nvarchar column we always specify the collation in the creation script e.g.

create table dbo.Example (

    Name nvarchar(250) collate Latin1_General_CI_AS not null
)

So, beware - there be dragons.