How to fix SQL Server Collation issue during installation?
I understand if you are here means you want to know the solution to fix your current collation issue. Thank you for being here.
I faced this issue in my current project and spent a long time fixing this issue. I realized rather than spending too much time fixing this, we go with a temporary workaround which is to create an empty database adds or modify collation that supports our project collation and then publish our database using Visual Studio Project. This way my work goes normal but still I’m not happy with this approach.
Started surfing over the internet and found a permanent solution. The reason for telling the above temporary workaround is “Some companies won’t allow to install or re-install SQL server” for them the above solution works.
Whoever can re-install their SQL Server then follow the below steps,
Step 1: Un-Install the current version of SQL Server including all Instances [Mandatory].
Step 2: Please keep the Collation Name that you want to configure.
Step 3: Download the SSMS and SQL Server from the following links,
SQL Server: https://www.microsoft.com/en-in/sql-server/sql-server-downloads
Step 4: Double Click on the downloaded .exe file and it will open a dialog, choose Basic/Custom option and continue.
Step 5: Once the initial installation is done then click on the Installation link and then click on New SQL Server (First link)
Step 6: This will open the following window,
Step 7: Product Key -> Click Next, License Terms -> Accept, and click Next, Microsoft Update -> Click Next , Product Updates and Install Setup files -> Click Next , Install Rules -> Click Next , Feature Selection -> Select required and click Next , Instance Configuration -> Click Next, Server Configuration -> Switch tab (Collation)
Note: Our article concentrates on collation so that not added any screenshots for the above flow.
Step 8: Here switch to the Collation tab,
Step 9: If you want to customize as per your collation then click on customize button.
Step 10: Choose either Windows Collation designator and sort order or SQL Collation used for backward compatibility.
Step 11: Choose Windows Collation designator and sort order and select a value from collation designator dropdown.
Based on the value from the collation designator dropdown, the following options will change accordingly. You can check as per your need among enabled options and then click OK.
Ex: Without checking any checkbox, the Default selected value is: Latin1_General_CI_AI
Note: CI — Case Insensitive, AI — Accent Insensitive.
Ex 2: With few options checked,
Note: CS— Case Sensitive, AS — Accent Sensitive.
Step 12: From after this continue other sections and complete the installation of your SQL server. Once after this create a new database.
Step 13: Check your collation on right-clicking the database -> Properties
Step 14: If it matches then Publish your database from the Visual Studio project or try your query where you faced the issue from SQL Server.
Hope this article will be helpful for you to fix the issue.
-Happy Learning :)
