SA0059B : Check for usage of collation different than the database default or the specified collation

Introduction

Ensure consistency by using the specified or database default collation in T-SQL scripts.

Description

Inconsistent collation settings can lead to unexpected query results, particularly when comparing text data. Using a different collation than the specified or database default can cause issues with sorting, indexing, and even matching strings in SQL Server databases.

For example:

-- Example of query with a collation inconsistency
SELECT column_name
FROM TableName
WHERE column_name COLLATE Latin1_General_CI_AS = 'SampleText';

This query may produce unexpected results if the database is using a different collation, such as SQL_Latin1_General_CP1_CI_AS. These discrepancies can lead to mismatched string comparisons and sorting anomalies.

Misaligned collation settings can cause case and accent sensitivity issues in comparisons.
Performance inefficiencies may arise because SQL Server cannot utilize indexes that depend on collation settings.

How to fix

To ensure consistency in your T-SQL scripts, align the collation settings with the database’s default collation or the specified collation requirements. Mismatched collations can lead to unexpected query results, performance issues, and incorrect data comparisons.

Follow these steps to address the issue:

1.Identify the collation currently used by your database with the following query: SELECT DATABASEPROPERTYEX('DatabaseName', 'Collation') .

2.Review and update your T-SQL script to use the identified collation or ensure it aligns with specific requirements by removing unnecessary COLLATE clauses.

3.If a COLLATE clause is necessary, explicitly specify the desired collation in the script, keeping it consistent with the database’s default when possible.

4.Test the script to verify that queries return expected results and that performance is optimized, considering index utilization.

For example:

-- Example of a corrected query using database default collation
SELECT column_name
FROM TableName
WHERE column_name = 'SampleText';

Scope

The rule has a Batch scope and is applied only on the SQL script.

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

5 minutes per issue.

Additional Information

There is no additional info for this rule.

Example Test SQL

/* Enter T-SQL script to test your analysis rule. */
CREATE TABLE TestTab
   (PrimaryKey int PRIMARY KEY,
    CharCol char(10) COLLATE French_CI_AS
   )

/* Enter T-SQL script to test your analysis rule. */
CREATE TABLE TestTab
   (PrimaryKey int PRIMARY KEY,
    CharCol char(10) COLLATE database_default
   )
SELECT *
FROM TestTab
WHERE CharCol LIKE N'abc'

CREATE TABLE TestTab (
   id int,
   GreekCol nvarchar(10) collate greek_ci_as,
   LatinCol nvarchar(10) collate latin1_general_cs_as
   )
INSERT TestTab VALUES (1, N'A', N'a');

SELECT *
FROM TestTab
WHERE GreekCol = LatinCol COLLATE greek_ci_as;

SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END) COLLATE Latin1_General_CI_AS
FROM TestTab

SELECT LatinCol COLLATE Latin1_General_CS_AS
FROM TestTab

Analysis Results

	Message	Line	Column
1	SA0059B : The used collation is different than the expected collation Cyrillic_General_CI_AS.	4	29
2	SA0059B : The used collation is different than the expected collation Cyrillic_General_CI_AS.	18	33
3	SA0059B : The used collation is different than the expected collation Cyrillic_General_CI_AS.	19	33
4	SA0059B : The used collation is different than the expected collation Cyrillic_General_CI_AS.	25	34
5	SA0059B : The used collation is different than the expected collation Cyrillic_General_CI_AS.	27	67
6	SA0059B : The used collation is different than the expected collation Cyrillic_General_CI_AS.	30	24

Analysis Rules