SQL Server Performance

Decide about collations

Discussion in 'SQL Server 2008 General DBA Questions' started by EsioNunes, Jun 2, 2010.

  1. EsioNunes New Member

    Dears:I’m consolidating 2 SQL Servers
    - SQL 2000: collation Latin1_General_CI_AI and
    - SQL 2005: collation SQL_Latin1_General_CP1_CI_AS -> it should be “CI_AI” but the setup as wrong in this machine.
    In a new SQL 2008 server. My databases are used for data in English and Portuguese and my servers are always in US-English.
    Despite I have the option to have each database with your own collation, I prefer change the collation from the databases from one server (I’ll do this with alter table for the existent tables), to have all my databases in SQL 2008 with the same collation, to avoid any problem while using the tempdb.
    For this moment, I have more database in SQL 2000 than SQL 2005, so I’m planning to setup the SQL 2008 with Latin1_General_CI_AI.
    My questions:
    Shall I really be concerned in collations conflicts with tempdb and have all this work to change the databases? I mean, only the question about the Accent Sensitive are really different, isnt’ it? Both use Latin (Code page 1252) am I right? Does anyone know why Microsoft setup the default collation for SQL 2008 as a SQL Collation, instead of the windows collation? Just for compatibility? Are any chance in future that this SQL location be discontinued? Because I heard on the past the SQL collations will be discontinued on the future.
    Since I’ll have an extra work now converting part of the databases, I want to be sure about the collation to use, so I won’t to worry about this again.Is the Latin1_General_CI_AI the best for me? Best regards,
    Ésio
  2. Adriaan New Member

    Mismatch between server and db collations is not really an issue as long as you stick to two rules ...
    For any temp table that is created in a sproc, for each alphanumeric column, you need to include either COLLATE your_collation or COLLATE DATABASE_DEFAULT in the temp table definition.
    If you run query that joins a user table/view with either a system table/view or a table/view from another database, on an alphanumeric column, add the same COLLATE clause to the ON clause for the field from the "other" table/view.
  3. EsioNunes New Member

    Adriaan, my problem is: part of my databases has vendor proprietary schemas and I can change any of this. I really need to attach the databases on the new server and let it run.
  4. Adriaan New Member

    In your initial post, you have no problem messing with column collations as supplied by the vendor.
    I'd take it up with the vendor, as they should anticipate the db being hosted on server instances with different default collations (especially regarding tempdb).
  5. satya Moderator

    As you had much of information from Adriaan, I would say to refer to Vendor based collation settings for database and make sure to check no issues when TEMPDB is used on resource intensive queries.

Share This Page