databases with different collations on same sever

  • sqlfriend

    SSC Guru

    Points: 52416

    We have two different databases on the same server.  The server collation is Latin1_General_CI_AS.

    Database A is the same as the server collation.

    Database B is a vendor product database using SQL_Latin1_General_CP1_CI_AS.

    I will have some joins and compares of data on the two different database and then to load data from Database B into Database A daily.

    When should I use the collate statement?

    The two database are only English, no other language or characters. Datatype are all like int, char, varchar, date etc. no uni code.

    Thanks

     

  • Jonathan AC Roberts

    SSCoach

    Points: 17269

    Really you should have the databases set to the same collation as the tempDB as if you create a temporary table you will have problems joining it to your database tables.

    In your case I would use database_default in the joins, then you will avoid problems joining to tables in tempDB too.

     

  • sqlfriend

    SSC Guru

    Points: 52416

    So this will only affect queries involving tempdb collation?

    What about when join other user database (not system ones) with different collations?

     

    Thanks

  • Jonathan AC Roberts

    SSCoach

    Points: 17269

    sqlfriend wrote:

    So this will only affect queries involving tempdb collation?

    What about when join other user database (not system ones) with different collations?

    Thanks

    If they are different collations then you will have problems joining text columns. But you still can use database_default in all your joins on text columns to get around this.

  • sqlfriend

    SSC Guru

    Points: 52416

    Thanks!

    So this will be only in join place?

    what about comparing statement like:

    Where a.firstname <>b.firstname,

    Etc.

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

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