Is it OK to Change System Locale after MSSQL2012 installation

Posted on

Question :

I have a MS SQL Server 2012 instance preinstalled on a Windows 2012 R2 production server. The Windows locale was English(EN-US), and SQL Server Collation is SQL_Latin1_General_CP1_CI_AS. The system handles all Chinese data with nvarchar fields just fine.

Later for some reason (unrelated to sql server) I may have to change default system locale of the Windows 2012 R2 server to Chinese. Will this cause any undesired side effect on the SQL server instance ?

Answer :

In general you should be ok. The only area that I am aware of that could be affected is SQLCLR, in which case the following conditions must be true:

  1. (
    The LCID of the OS does not match the LCID of the default Collation of the Database where the Assembly is loaded,

    OR

  2. The default Collation of the Database where the Assembly is loaded is either a binary Collation, or is any combination of the following: case-sensitive, accent-insensitive, width-sensitive, or Kana-type-sensitive
    ),

    AND

  3. custom SQLCLR code is being used

  4. SqlString is being used as an input parameter type
  5. The SqlString parameter is being concatenated inline with a string while not using either the Value property or the ToString() method (i.e. "some string" + SqlStringInputParam;)
  6. Or, possibly using Regular Expressions / Regex.

To be clear, this issue relates to the default Collation of the Database in which the Assembly is loaded, not the Instance- / Server- level Collation, nor even the Collation of any string column or any string literal or variable with a Collation set via the COLLATE keyword.

If your Database has a default Collation of SQL_Latin1_General_CP1_CI_AS because that is what the Instance-level Collation is, then neither # 1 nor # 2 above are true. And in that case, IF there is any SQLCLR code in that Database that matches items 4 – 6 above, then you wouldn’t see any problem. BUT, changing the Locale of the OS would make item # 1 above true, in which case if there is any SQLCLR code that matches item 4 – 6 above, then that code will start to throw “Collation mismatch” exceptions.

Admittedly there is a fairly low probability of running into this. But, it has happened, so it’s best to be aware of it.

Nothing will happen to your SQL Server installation if you change the Windows system locale, because it was installed with the specific collation SQL_Latin1_General_CP1_CI_AS.

Leave a Reply

Your email address will not be published.