• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 813
  • Last Modified:

How the different in SQL Collation affect the application operation? How to resolve?

This is using few MS SQL 2008 R2 servers. There are 4 servers altogether. By default, the collation is set with "Latin1_General_CI_AI". Some how, there is one SQL server was found to set with SQL_Latin1_General_CP1_CI_AS. Do they matched?

My manager doesn't feel comfortable with the difference, and change is a MUST. How should we go and make the changes? Does change the collation directly is the way to go? Any side-effect in doing so? What would be the better approach as all the above 4 servers are in production? Hope any changes done can minimize the impact on production.

Thanks in advance
1 Solution
Dale BurrellCommented:
2 questions for you, do you ever run cross/server queries? And what collation is the database on the server which is SQL_Latin...?

Those collations are not the same and if you try and run a query which joins data from one collation with the other then you will get issues. The *normal* issue people face is the server is one collation and a database another, which is fine until you do a query which uses tempdb and then you get a collation conflict.

However assuming your database is the same collation as the server, and assuming you don't do any cross server queries you will probably be OK.

This explains how to change it if you need to http://msdn.microsoft.com/en-us/library/ms179254.aspx

Whether you need to script your databases depends on whether their collation needs to change or not.

If you can't script the database and need to change the collation then I think it gets tricky. http://msdn.microsoft.com/en-us/library/ms175835(v=sql.100).aspx

Hope that helps.
By the looks of things Accent Sensitivity is turned on only on the SQL_Latin1 collation. This could mean that you searches act differently if you use accents in the database.

If you restore the database the collation is kept and if the collation on the server is different different this may also cause issues.
CI means Case Insensitive
AI means Accent Insensitive

CP1 means Code Page 1
CI means Case Insensitive
AS means Accent Sensitive

Case and Accent sensitivity settings affect how SQL Server compares characters... for example, is 'a' = 'A'? Depends on case sensitivity. Likewise with characters that have accents (French uses these a lot and so on). Your queries might behave differently depending on these settings. For example: SELECT ..... WHERE FirstName LIKE 'A%' will return 'Albert' but not 'albert' on a Case Sensitive collation.

Some collations are compatible, and others are not. If you have to do cross server queries, then you may get issues with doing JOINs or other comparisons if the collations are not compatible.
MezzutOzilAuthor Commented:
I'm not SQL expert, just a normal DB admins. My SQL developer has successfully changed the collation and now the sql is working fine. Many thanks for your explanation.

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now