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

Posted on 2012-08-16
Last Modified: 2012-08-21
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
Question by:MezzutOzil
    LVL 21

    Expert Comment

    by:Dale Burrell
    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

    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.

    Hope that helps.
    LVL 16

    Expert Comment

    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.
    LVL 30

    Accepted Solution

    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.

    Author Closing Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
    This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.

    758 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now