Post reply

collation

  • May 2, 2011 at 4:21 pm

    #250144

    I just notice in one of our database, some tables are in collation: Latin1_General_CI_AS

    Some tables are in collation: SQL_Latin1_General_CP1_CI_AS

    What are the difference?

    As a dba, what needs to pay attention to the difference collation in every day maintenance work?

    Thanks

  • May 2, 2011 at 8:17 pm

    #1318825

    Might this help you

    Latin1_General_CI_AS :- Latin1-General, case-insensitive, accent-

    sensitive, kanatype-insensitive, width-insensitive

    SQL_Latin1_General_CP1_CI_AS:- Latin1-General, case-insensitive,

    accent-sensitive, kanatype-insensitive, width-insensitive for Unicode

    Data, SQL Server Sort Order 52 on Code Page 1252 for non-Unicode Data

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 3, 2011 at 9:45 am

    #1319182

    It helps a little bit.

    But in the process of upgrade server, shall we keep that or change it, I see sql server default is different from what we have, if changed, does it cause errors?

    Thanks

  • May 3, 2011 at 10:00 am

    #1319196

    i think you could run into problems if the default colaltion ( which is what tempdb would have) is different:

    this simple exampel raises this error:

    Msg 468, Level 16, State 9, Line 3

    Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the charindex operation.

    WITH MYSampleData AS

    (

    --SELECTING CAPITAL 'A'

    SELECT CHAR(65) COLLATE Latin1_General_CI_AS AS ONE,

    CHAR(65) COLLATE SQL_Latin1_General_CP1_CI_AS AS TWO

    )

    SELECT

    ONE,

    TWO,

    CHARINDEX(ONE,TWO),

    CASE

    WHEN ONE = TWO

    THEN 'MATCH'

    ELSE 'NO MATCH'

    END

    FROM MYSampleData

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • May 3, 2011 at 10:06 am

    #1319201

    So should we just keep the colllation of what the older server have in the upgrade process?

    Thanks

  • May 3, 2011 at 10:32 am

    #1319221

    SQLMyFriend (5/3/2011)

    So should we just keep the colllation of what the older server have in the upgrade process?

    Thanks

    Only you can make that decision. My moto is "If it is not broke do not fix it". but again we do not fully understand the conditions you are working under, how much of existing SP / Table / Column definitions would have to be changed, and what happens during all the down time while that is taking place. And if the result of the changes is not satisfactory, how do you get back to the original.

    Like I said only you can decide.

    If everything seems to be going well, you have obviously overlooked something.

    Ron

    Please help us, help you -before posting a question please read[/url]
    Before posting a performance problem please read[/url]

  • May 4, 2011 at 5:45 am

    #1319658

    I had this "issue".

    The database collation had changed at some point, so the older character columns (not tables - tables don't have collations) had one collation and newer ones had another. The reason being that new columns where no collation is specified take the database collation by default.

    As far as I can tell, tempdb takes the server collation - which led to problems when we moved the database to a new server with a different collation. Basically any stored procedures which created temp tables and then joined existing tables to those temp tables on character columns started throwing errors.

    I decided to fix the issue once and for all, by changing everything to use one collation (that of the new server). This involved:

    Dropping all schemabound functions and views

    Dropping all check constraints

    Dropping all computed columns

    Changing the database collation

    Disabling/dropping all indexes on character columns with old collation

    Changing the collation on all character columns with old collation

    Rebuilding/recreating all indexes previously disabled/dropped

    Adding all computed columns

    Adding all check constraints

    Adding all schemabound functions and views.

    All in all, it took the best part of a day on a database with some 100 tables. Sounds fun, doesn't it 😉

    Duncan

  • May 4, 2011 at 9:26 am

    #1319872

    That sounds just like what I heard from my former collegue.

    He told me it will be a great pain when upgrading- you ignore the collation of older server, and later found the new server has a different default collation.

    I hope there are some procedures that MS can make it easier.

    I guess I will have to keep what ever in the old server the collation is, and install on new server that collation. We have tons of tables, columns, sp in the database developed by developer, I don't have the courage to drop them all and recreate them.

    Thanks

  • May 5, 2011 at 6:52 am

    #1320334

    SQLMyFriend (5/4/2011)

    That sounds just like what I heard from my former collegue.

    He told me it will be a great pain when upgrading- you ignore the collation of older server, and later found the new server has a different default collation.

    I hope there are some procedures that MS can make it easier.

    I guess I will have to keep what ever in the old server the collation is, and install on new server that collation. We have tons of tables, columns, sp in the database developed by developer, I don't have the courage to drop them all and recreate them.

    Thanks

    As stated above, the main issue with server collation is tempdb. If your stored procedures use temp tables and join to them on character columns, you may have to rewrite the stored procedures to include the collation in the temp table definition. Other than that, the database collation will be the same as it was on the old server, so anything internal to the database will be the same as it was before.

    In my case, I'd been finding the mixture of collations a bit of a pain for several years and saw the moving of the database as an opportunity to fix the issue once and for all. Horses for courses and all that.

    Cheers

    Duncan

  • May 5, 2011 at 9:21 am

    #1320490

    Is it also related with using system stored procedures ?

  • May 5, 2011 at 9:38 am

    #1320499

    SQLMyFriend (5/5/2011)

    Is it also related with using system stored procedures ?

    I don't see why, although if you're joining to system table-valued functions that might cause issues.

Viewing 11 posts - 1 through 11 (of 11 total)

You must be logged in to reply to this topic. Login to reply