none
Provision new database diffrent to Instance collation

    Question

  • Hi

    SQL Version 2016 CU7 Standard edition

    I have requirement to provision new database collation  SQL_Latin1_General_CP850_BIN. But instance collation is Latin1_General_CI_AS. In this situation tempdb will burn with conversion SQL  and get impact of DB and instance performance. I understand same collation database and instance would be the best practise.

    Therefore what the best tech advice to accomadate this issue.

    1. having new instance and provision . (This service is VM server and not good to install other instance on same server.)

    2. What is the best practise to provision a db if the DB has different collation over instance collation .

    Many thanks

       

    Monday, April 23, 2018 1:39 AM

All replies

  • >>>I understand same collation database and instance would be the best practise

    Correct

    >>>2. What is the best practise to provision a db if the DB has different collation over instance collation .

    You will need to use a hint COLLATE

    If your JOIN condition on VARCHAR/CHAR columns and have diff collation  you would need 

    SELECT <columns> FROM customer JOIN #tmp ON customer.cust=#tmp.cust  COLLATE database_default


    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 23, 2018 4:02 AM
    Moderator
  • Hi Uri

    This can do when I do. but practical already develop application to do so. What is best advice you could do or some one can..Greatly appreciated .

    regards

    Monday, April 23, 2018 4:48 AM
  • Hi Uri

    This can do when I do. but practical already develop application to do so. What is best advice you could do or some one can..Greatly appreciated .

    regards

    If you want to avoid using COLLATE clause then you need to modify the collation of the db to match the instance

    For this if db is just created you can use ALTER DATABASE.. COLLATE statement

    https://docs.microsoft.com/en-us/sql/relational-databases/collations/set-or-change-the-database-collation?view=sql-server-2017

    If db has been already there with some data, then it involves multiple stages to ensure existing data is also stored in the modified collation

    http://www.itprotoday.com/microsoft-sql-server/seven-step-process-changing-databases-collation


    Please Mark This As Answer if it solved your issue
    Please Vote This As Helpful if it helps to solve your issue
    Visakh
    ----------------------------
    My Wiki User Page
    My MSDN Page
    My Personal Blog
    My Facebook Page

    Monday, April 23, 2018 5:13 AM
  • If you can have new instance provisioned with the said collation you should do it but again that would mean more license or load on existing hardware

    There is no best practice as such you can create different database on the instance with collation SQL_Latin1_General_CP850_BIN. This would be better in terms of using collate command every time you join with other databases


    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Monday, April 23, 2018 6:26 AM
    Moderator
  • Look, you can set the database with a collation as  the instance has , but set the need collation on the column level only...

    Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

    MS SQL optimization: MS SQL Development and Optimization
    MS SQL Consulting: Large scale of database and data cleansing
    Remote DBA Services: Improves MS SQL Database Performance
    SQL Server Integration Services: Business Intelligence

    Monday, April 23, 2018 9:25 AM
    Moderator
  • Hi Shanky Thanks for the inform .Currently no plain to install other instance for same server as already running a instance. Further more no option to provision new instance on  new server .

    Therefore, as long as we do NOT join any quarries  with other databases(default collation as instance collation) , what the advice provision different collation DB over default instance collation.

    Many thanks

    Wednesday, April 25, 2018 10:45 PM
  • Hi Uril

    Thanks for your advice . When  setup column level collation as per the business need, then, in long run if business need to change to different DB collation, the "alter database <> collate "  that command does not change column level collations . All are remain as it is . As per my feeling, this will going to be complicated in long run. We need to find 100 over tables one by one column collation and change accordingly . any best practice idea would be appreciated . many thanks




    • Edited by ashwan Wednesday, April 25, 2018 10:52 PM
    Wednesday, April 25, 2018 10:50 PM
  • Hi Shanky Thanks for the inform .Currently no plain to install other instance for same server as already running a instance. Further more no option to provision new instance on  new server .

    Therefore, as long as we do NOT join any quarries  with other databases(default collation as instance collation) , what the advice provision different collation DB over default instance collation.

    Many thanks

    I would suggest DB with different collation.

    Cheers,

    Shashank

    Please mark this reply as answer if it solved your issue or vote as helpful if it helped so that other forum members can benefit from it

    My TechNet Wiki Articles

    MVP

    Thursday, April 26, 2018 4:49 AM
    Moderator