Collation Question

• r5d4

  SSCrazy

  Points: 2519

  More actions

  March 22, 2018 at 9:14 am

  #336742

  Hiya,

  I have a sql 2012 server set up with collation Latin1_General_CI_AS  (default).
  A vendor is performing an upgrade from 2008R2,

  The database being restored on is in collation SQL_Latin1_General_CP850_CI_AI (probably from a previous migration).

  So I can tell the only difference is the accent sensitivity. fn_helpcollations tells me the rest, as below.

  SELECT description FROM sys.fn_helpcollations() 
WHERE name = 'SQL_Latin1_General_CP850_CI_A';

  -- Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 44 on Code Page 850 for non-Unicode Data

  SELECT description FROM sys.fn_helpcollations() 
WHERE name = 'Latin1_General_CI_AS';

  -- Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

  Is this a problem? I know that tempdb being in a different collation can cause problems, but how do I determine how MUCH of a problem?
  Am i worrying over nothing?

  Thanks all
  R

• Jeff Moden

  SSC Guru

  Points: 1003853

  More actions

  March 24, 2018 at 5:03 pm

  #1984492

  r5d4 - Thursday, March 22, 2018 9:14 AM

  Hiya,

  I have a sql 2012 server set up with collation Latin1_General_CI_AS  (default).
  A vendor is performing an upgrade from 2008R2,

  The database being restored on is in collation SQL_Latin1_General_CP850_CI_AI (probably from a previous migration).

  So I can tell the only difference is the accent sensitivity. fn_helpcollations tells me the rest, as below.

  SELECT description FROM sys.fn_helpcollations() 
WHERE name = 'SQL_Latin1_General_CP850_CI_A';

  -- Latin1-General, case-insensitive, accent-insensitive, kanatype-insensitive, width-insensitive for Unicode Data, SQL Server Sort Order 44 on Code Page 850 for non-Unicode Data

  SELECT description FROM sys.fn_helpcollations() 
WHERE name = 'Latin1_General_CI_AS';

  -- Latin1-General, case-insensitive, accent-sensitive, kanatype-insensitive, width-insensitive

  Is this a problem? I know that tempdb being in a different collation can cause problems, but how do I determine how MUCH of a problem?
  Am i worrying over nothing?

  Thanks all
  R

  Why doesn't the vendor use the previous collation so that you don't have to worry about such things?  And, yea... changing accents could definitely cause a problem depending on the use of extended characters.  I don't know for sure, but I believe any database that you want to migrate by simply doing a restore is going to have the old collation.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)
  

• Sergiy

  SSC Guru

  Points: 110206

  More actions

  March 27, 2018 at 1:49 am

  #1984619

  In each temp table a character column must have the same collation as columns it's matched to in JOIN or WHERE clauses.

  To make the code independent from tempdb collation every CREATE #table statement must explicitly specify appropriate collation for all char columns.

  If you cannot see it in code - then yes, you are facing a problem.

  You have to make sure tempdb, and probably other system db's, have the same collation as they used to have.

  _____________
  Code for TallyGenerator