Click or drag to resize

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

The topic describes the SA0096 analysis rule.

Message

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

Description

The rule verifies that the collation of the current database is the same with the collation of the [model] database.

Having a database collations different from the model database can cause collation conflicts which may prevent code from executing. For example, when a table is joined to a temporary table, SQL Server may experience a collation conflict error if the user-defined database collation and the model database collation are different. This is because temporary tables are created in tempdb, which is created at server startup with the collation of the model database.

You can consider the following:

  • Create a new database with the same collation as the model database and import the data form the given database in the new database.

  • Rebuild the system databases to use the same collation as your database.

  • Review and modify any SQL code that joins user tables to temporary tables. To prevent collation conflicts, you can add and the 'COLLATE database_default' clause to the joined string columns of the temporary table, or by modifying the temporary table definition script and explicitly set the collation to each string column using the COLLATE clause to be the same as your database’s collation.

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

Scope

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

Parameters

Rule has no parameters.

Remarks

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

Categories

Design Rules

Additional Information
See Also

Other Resources