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

The topic describes the SA0096 analysis rule.


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


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.

How to fix

<para>Consider the following: </para>

<list class=”bullet”> <listItem><para>Create a new database with the same collation as the model database and import the data form the given database in the new database. </para></listItem>

<listItem><para>Rebuild the system databases to use the same collation as your database.</para></listItem>

<listItem><para>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.</para>

<code language=”SQL”>

CREATE TABLE #EmployeeResumes


LName nvarchar(25) COLLATE database_default,

FName nvarchar(25),

Resume xml( DOCUMENT HumanResources.HRResumeSchemaCollection)



FROM HumanResources.EmployeeResumes er

INNER 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.

Effort To Fix
20 minutes per issue.

Design Rules, Code Smells

Additional Information
See Also

Other Resources