question

nkelleher avatar image
nkelleher asked

sql collation

i have a sql 2008r2 server with collation of Latin1_General_CI_AS. i have a request for a new database with collation of Latin1_General_100_CI_AS_KS - is there any potential issue having a db with different collation to server? is there any advantage in putting the new database in a drive of it's own on the server
collationsql 2008r2
1 comment
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You have some good answers below. This web site runs by you voting and marking the best answers. For each helpful answer below, indicate this by clicking on the thumbs up symbol next to those answers. If any one answer lead to a solution, indicate this by clicking on the check mark next to that answer. This helps indicate your solution for anyone else with the same question.
0 Likes 0 ·
David Wimbush avatar image
David Wimbush answered
It's not ideal for a database to have a different collation than the server but it's not necessarily a show stopper. I've seen several cases of this running quite happily, although cross-database queries will have to explicitly specify the collation in string comparison expressions in WHERE clauses and JOINs. Putting the file on a different drive won't change this issue at all. There's a bit more detail here: http://www.brentozar.com/blitz/database-server-collation-mismatch/
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

srutzky avatar image
srutzky answered

There is no inherent danger in having different collations across databases. You can even have different collations across various columns in the same table. And in fact, if you look at any of the system catalog views (`sys.tables`, `sys.objects`, `sys.columns`, and so on) you will find that they have several collations across the columns, and for various reasons.

The issues you may potentially run into depend on what you are doing in your queries, and if the database is a contained database.

  1. A common issue is that `[tempdb]` default collation matches the server collation, which will now not match the new DB's collation. If code in the new DB creates a local or global temp table and doesn't specify the `COLLATE` clause, the fields will take on the collation of `[tempdb]`. If you run into collation mismatch errors, you need to specify `COLLATE DATABASE_DEFAULT`. This does not apply to Table Variables as those take on the collation of the current database when they are declared. This applies slightly differently to contained databases.
  2. If you are using a Contained Database then the rules change slightly: the default collation for string fields in temp tables is taken from the current database, hence is inherently already using `DATABASE_DEFAULT`. For more info, please see Contained Database Collations on MSDN.
  3. If you have cross-database queries that compare string fields between tables in both DBs and the column collations between the fields do not match, then you will have essentially the same scenario as noted with temp tables above. The difference here is that these tables already exist so you are not going to alter either column to change its collation. But the fix is still essentially the same: specify `COLLATE DATABASE_DEFAULT` or even `COLLATE {desired_collation_name}` on the field(s) in the query with the non-matching collation. You just need to decide which collation should be preferred between the two in order to determine which field will get the `COLLATE` clause.
  4. If you run the same code in both DBs, you might could see variations in how certain statements behave since string literals and local variables take on the collation of the DB where the code is defined (or being run if an ad hoc query). So:

    SET @LocalVar NVARCHAR(50);
    SELECT @LocalVar = TOP (1) something
    FROM   some table;
    IF (@LocalVar = N'string literal')
    BEGIN
        some statements;
    END;


    could potential behave differently when run in both DBs, even if the same initial value of the same collation was used to set the value of `@LocalVar`. This is not a huge concern, just something to be aware of. Please see the documentation page for Collation Precedence for more details.

  5. Comparing string values in local tables against system catalog views. Some fields in some catalog views take on the local DBs collation, especially related to things that you name that reside in that DB: tables, columns, indexes, etc. So, `sys.tables.name` will have the local DBs collation. Again, just something to be aware of.

The question about where to physical store the data file(s) for the new DB is entirely irrelevant to this Question about collations.

10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.