SA0096 : The collation of the current database does not match that of the model database

Collation issues occur when the collation setting of a user-defined database does not match the collation of the model database. These conflicts can lead to errors during query execution in SQL Server.

For example:

1-- Example of problematic join that may produce a collation conflict
2SELECT *
3FROM UserDefinedTable AS a
4JOIN #TempTable AS b ON a.Name = b.Name;

This example can result in a collation conflict if UserDefinedTable uses a collation different from that of #TempTable, which inherits its collation from the tempdb, aligned with the model database.

• Collation conflicts can prevent execution of queries, particularly during joins with temporary tables.

• Such conflicts may necessitate costly and complex workarounds, such as specifying explicit collation in queries.

Address collation conflicts that arise from mismatched database collations to prevent query execution errors.

Follow these steps to address the issue:

1. Create a new database with the same collation as the model database. Use CREATE DATABASE specifying the desired collation, and import the data from the existing database into the new one.

2. Rebuild the system databases to use the same collation as your database if necessary. This step involves recreating the necessary databases using a consistent collation.

3. Review and modify any SQL code that joins user tables to temporary tables by specifying the ‘COLLATE database_default’ clause to ensure consistent collation in join operations.

4. Modify the temporary table definition script to explicitly set the collation for each string column using the COLLATE clause, ensuring it matches your database’s collation.

For example:

1CREATE TABLE #EmployeeResumes 
2(
3    LName nvarchar(25) COLLATE database_default, 
4    FName nvarchar(25), 
5    Resume xml(DOCUMENT HumanResources.HRResumeSchemaCollection) 
6);
7SELECT * 
8FROM HumanResources.EmployeeResumes er 
9INNER JOIN #EmployeeResumes tmp ON er.FName = tmp.FName COLLATE Latin1_General_CI_AS;

The rule has a ContextOnly scope and is applied only on current server and database schema.

Rule has no parameters.

The rule requires SQL Connection. If there is no connection provided, the rule will be skipped during analysis.

Design Rules, Code Smells

Set or Change the Database Collation

Rebuild System Databases

Other Resources