SA0059A : Check database for objects created with different than default or specified collation

Introduction

Inconsistency in database object collation settings, which may lead to unexpected query results or performance issues.

Description

In SQL Server, collation determines how string comparisons are performed. It’s crucial that database objects share the same collation settings to ensure consistency and performance. Issues arise when objects use different collations than the database default or specified setting.

Consider the following scenario:

-- Example of potential collation issue
CREATE TABLE TestTable (Name NVARCHAR(100) COLLATE Latin1_General_CI_AS);

In this example, the COLUMN Name is explicitly defined with a collation different from the database default. This discrepancy might cause errors or unexpected results when joining with other tables or performing string comparisons.

Collation mismatches can lead to query errors or require explicit collation handling in joins and comparisons.
Differing collations can negatively affect query performance, as additional computational resources are required for conversions.

How to fix

This section outlines steps to resolve discrepancies in database object collation settings, ensuring consistent and optimized string comparisons.

Follow these steps to address the issue:

1.Identify the current collation of the database by executing the query: SELECT DATABASEPROPERTYEX('YourDatabaseName', 'Collation') .

2.Inspect the collation settings of the affected database objects using: SELECT COLUMN_NAME, COLLATION_NAME FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = 'YourTableName' .

3.If discrepancies are found, recreate the object with the default database collation by using the ALTER TABLE statement as follows:

4.Alter the column to match the default collation: ALTER TABLE YourTableName ALTER COLUMN YourColumnName NVARCHAR(100) COLLATE YourDatabaseCollation .

5.Validate the changes by re-running the previous inspection query to ensure the collation settings are consistent.

For example:

-- Example of corrected collation setting
ALTER TABLE TestTable ALTER COLUMN Name NVARCHAR(100) COLLATE DATABASE_DEFAULT;

Scope

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

Parameters

Name	Description	Default Value
Collation	Specific collation to check for.	Database Collation

Remarks

The rule requires Analysis Context. If context is missing, the rule will be skipped during analysis.

Effort To Fix

13 minutes per issue.

Additional Information

There is no additional info for this rule.

Analysis Rules