Summary: You can encounter collation issues while restoring backup file, executing cross-database queries on tables, or migrating data from SQL Server to another software. Such issues occur when the database storage engine fails to read collation settings. In this article, we will learn about collation issues in SQL Server and see how to resolve them. If there is corruption in the database file, then use the SQL repair software mentioned in this article to repair the corrupted file.
Collation in SQL Server refers to a set of character and character encoding rules that determine how data is stored and compared in server. In other terms, collation in SQL Server specifies the bit patterns that signify every character in the regional character set and identifies rules for organizing and comparing the data. There are more than 5000 collations in MS SQL, which vary with the language of the country and region.
There are various options associated with collations, such as Case Sensitive, Accent Sensitive, Kana Sensitivity, and Width Sensitivity to apply sorting rules, case, and accent sensitivity properties to the data in the database file. You can apply collation at four levels – Instance, Database, Column, and Query.
If you try to compare, sort, or join columns that have incompatible or incorrect collations, you can encounter errors. For example, while trying to execute queries on two tables, collation conflicts can occur, leading to errors, like Cannot resolve the collation conflict between “SQL_Latin1_General_CP1_CI_AS” and “Arabic_CI_AS” in the equal to operation. Sometimes, collation conflicts can occur if the SQL database on which you have applied collation settings is corrupted. In this article, we will learn how to fix collation issues in MS SQL Server.
How to Check Collation Settings of SQL Server Database?
You can run the below command to check the collation settings of your database:
SELECT name, collation_name FROM sys.databases;
Alternatively, you can use the DATABASEPROPERTYEX system function to check database-level collation settings (see the below example).
SELECT CONVERT (varchar(256), DATABASEPROPERTYEX('database15','collation'));
To check column-level collation setting, run the below command:
SELECT name, collation_name FROM sys.columns WHERE name = N'<insert character data type column name>';
Methods to Resolve Collation Issues in MS SQL Server
The collation conflict errors and issues can occur when two databases with different collations are used in a query. You can try the below methods to fix the collation issues in MS SQL.
Method 1: Change the Collation of Entire Database
Several SQL Server users have confirmed that they’re able to resolve the collation issues by changing the collation of the entire database. Setting the uniform collation rules for the database helps prevent collation conflict errors. To change the database collation, you must have the ALTER permission to access the database.
Note: When you change the database collation, the char, varchar, and text columns in the system tables and their parameters and user-defined data types are also changed to the new collation. If you experience server-level collation during MS SQL Server installation, then you can try to change the server collation.
Here are the steps to change the collation of the entire database:
- In SQL Server Management Studio (SSMS), open the Object Explorer, connect to the instance of the SQL Server Database Engine, expand that instance, and then expand Databases.
- Right-click on the database (of which you need to change the collation) and select Properties.
- On the Properties window, click on Options and click on the dropdown option to select a collation.
- Select the Collation and then click OK.
Alternatively, you can use the COLLATE clause in the ALTER DATABASE statement to change the database collation. Here’s how to do so:
USE master;
GO
ALTER DATABASE database15
COLLATE French_CI_AS ;
GO
Note: You can use this method to resolve the collation-related issues while restoring or migrating the database from one server to another.
Method 2: Change Collation of a Specific Column
If you are facing collation issues while sorting and comparing two tables, then you can simply alter the collation of a specific column to match the collation of other columns. To do this, run the below command:
ALTER TABLE Employ
ALTER COLUMN FirstName NVARCHAR(50) COLLATE French_CI_AS;
Note: This method is suitable for small-sized databases that contain fewer tables.
Method 3: Use the COLLATE Clause
If you want a quick solution without changing the database/column collation, then you can use the COLLATE clause in the query. In this, a common collation is applied in the query which helps in resolving collation conflicts. This way only the specific operation in query uses the new collation instead of changing the collation of column or database. Here is how to do this:
SELECT *
FROM EmployA A
JOIN EmployB B
ON A.Name COLLATE Latin1_General_BIN = B.Name;
What if the SQL Database is Corrupted?
You may also encounter collation issues due to inconsistencies or corruption in the SQL database. In such a case, an easy solution is to use Stellar Repair for MS SQL – a professional SQL repair tool that can effectively repair SQL database (NDF/MDF) files. The tool can recover collations, tables, indexes, and other objects from the database with complete precision. It supports 4500 collations and 5508 collation code pages. The tool can even recover deleted records from the corrupted database file. Also, it provides an option to select database collations for severely damaged or corrupted database file.
To Conclude
Collation conflicts or issues can occur while restoring or migrating a SQL database or performing operations (Join) on Non-Unicode datatypes. The issues occur when the server fails to match the collation value with the default/set collation. To resolve the issues, you can follow the methods mentioned above. If the SQL database is corrupted, you can use Stellar Repair for SQL to repair the database. It can help you in recovering all the objects from the database, including the collation settings for non-Unicode columns.
