locked
Can DB use new collation different to Instance level collation? RRS feed

  • Question

  • Can DB use new collation different to Instance level collation?

    Hi My sql server instance 2016 CU7.   This DB going ti enable on AG group(alwayon ). Instance Collation is SQL_Latin1_General_CP1_CI_AS. Now new DB need to come to instance with different collation

    Q1. is that reaccommodated practise to have database with different collation overwriting instance level collation.

    Q2. As per my understanding done it impact instance level performance or /error thronging situations due to temp DB use for sorting . Is that correct.

    Q2.What is the alternative if new DB can’t change their new collation . Db has option "Containtment type" does it set to "partial"

    will that good go with different collations

    Expert ideas are  highly appreciated 

    thanks


    • Edited by ashwan Friday, August 16, 2019 1:23 AM
    Thursday, August 15, 2019 11:58 PM

Answers

  • Hi ashwan,

    >> Can DB use new collation different to Instance level collation?

    Yes, we can. However, changing the database collation is utterly painful. If you run ALTER DATABASE COLLATE, that only changes the collation of the system tables, so that does not resolve the problems with the temp tables. The rest you need to do yourself, and it's not fun as indexes has to be dropped and recreated etc. So in the end, staying with the SQL collation is the only reasonable choice. Remember, in AG both replicas have to be the same collation. Tempdb uses server collation and if you create temp tables etc. then they will use tempdb's collation and your cross joins reference etc will fail. An exception to this would be if you use contained databases wherein they use the DB's collation.

    Please refer to Set or Change the Database Collation, Change Collation After setting up Always On Availability Group to get more information.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 16, 2019 2:49 AM
  • Hi ashwan,

    >> change "Containtment type" of the DB (2016) will that resolve the issue or still remain

    Tempdb uses server collation and if you create temp tables etc. then they will use tempdb collation , but contained databases wherein they use the DB's collation.
    It seems resolve the issue, but I still suggest you staying with the SQL collation.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 16, 2019 10:22 AM

All replies

  • Hi ashwan,

    >> Can DB use new collation different to Instance level collation?

    Yes, we can. However, changing the database collation is utterly painful. If you run ALTER DATABASE COLLATE, that only changes the collation of the system tables, so that does not resolve the problems with the temp tables. The rest you need to do yourself, and it's not fun as indexes has to be dropped and recreated etc. So in the end, staying with the SQL collation is the only reasonable choice. Remember, in AG both replicas have to be the same collation. Tempdb uses server collation and if you create temp tables etc. then they will use tempdb's collation and your cross joins reference etc will fail. An exception to this would be if you use contained databases wherein they use the DB's collation.

    Please refer to Set or Change the Database Collation, Change Collation After setting up Always On Availability Group to get more information.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 16, 2019 2:49 AM
  • Hi Cathy

    MAy thank for the advise. How ever change "Containtment type" of the DB (2016) will that resolve the issue or still remain? Do you have some information on this. 

    Many thanks

    Friday, August 16, 2019 10:07 AM
  • Hi ashwan,

    >> change "Containtment type" of the DB (2016) will that resolve the issue or still remain

    Tempdb uses server collation and if you create temp tables etc. then they will use tempdb collation , but contained databases wherein they use the DB's collation.
    It seems resolve the issue, but I still suggest you staying with the SQL collation.

    Best regards,
    Cathy 

    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to  MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Friday, August 16, 2019 10:22 AM