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.


































Comments











Post a Comment



















Popular posts from this blog









Trimming strings in action parameters in ASP.Net Web API 





















In standard MVC it's very easy to create a model binder to trim all of the strings passed into action methods. The approach I used for that was from here:  http://blogs.taiga.nl/martijn/2011/09/29/custom-model-binders-and-request-validation/ . It works well and it trims the properties of models too.   The problem came when I tried to do the same thing for Web API. I added a similar StringTrimmingBinder and registered it as a service using the very non-discoverable API:   var  provider = new  SimpleModelBinderProvider(      typeof ( string ),       new  StringTrimmingModelBinder()); GlobalConfiguration.Configuration.Services.Insert(      typeof (ModelBinderProvider),       0,       provider);   As a side note, I have no idea how you're supposed to find out how to register a model binder without opening up Bing and Googling it.   Anyway, this worked great when the parameters to the action were actually strings but doesn't work with complex types as parameters. Now, I'v...








Read more










Full text search in Entity Framework 6 using command interception




















Image







 Background  One of the main problems we encountered using Entity Framework was its lack of support for full text search. Our search function is based around a series of processors that attempt to match the incoming phrase against a set of patterns that we're expecting (10+). For example, there's one for dates and times, recent financial quarters as well as more domain specific ones. Everything is cached and so is really fast, except when it isn't:      Here's the monitoring chart for our SQL DB in Azure   That's a spike to 100% resource utilisation because we fall through to a phrase search as a last resort (which cannot be cached) - it's exceedingly rare but it still hurts.   Before EF, the app used full text search and it was one of the casualties when the app was migrated to EF4 several years ago. The lack of full text search has been getting more and more painful as the amount of data has grown. We really needed full text search back...    Command intercept...








Read more










Composing Expressions in C#





















This post follows on from  Reusing predicates in Entity Framework .   In my previous post I introduced the "and" extension method which allowed me to create a new entity framework safe expression from two existing expressions. Here's how we make that happen:   The code is from a blogpost from Colin Meek from 2008:  InvocationExpression and LINQ to Entities .   I'll just dump my version of the code here. The idea is exactly the same (even using the visitor as an immutable stack):   public  class  InvocationExpander : ExpressionVisitor {     private  readonly  ParameterExpression _parameter;     private  readonly  Expression _expansion;     private  readonly  InvocationExpander _previous;       public  static  T Expand<T>(T expr)         where T : LambdaExpression     {         return  (T) new  InvocationExpander().Visit(expr);     }      public  InvocationExpander() { }      private  InvocationExpander(ParameterExpression parameter, Expression expansion, Invocati...








Read more