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

The topic describes the SA0059B analysis rule.

Message

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

Description

The rule checks T-SQL script for different than the database default or the specified collation.

How to fix

Review the script and the reason for a different collation than the current database’s default collation to be used.

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

Design Rules, Bugs

Additional Information

There is no additional info for this rule.

Example Test SQL
SQL
 1/* Enter T-SQL script to test your analysis rule. */
 2CREATE TABLE TestTab
 3   (PrimaryKey int PRIMARY KEY,
 4    CharCol char(10) COLLATE French_CI_AS
 5   )
 6
 7/* Enter T-SQL script to test your analysis rule. */
 8CREATE TABLE TestTab
 9   (PrimaryKey int PRIMARY KEY,
10    CharCol char(10) COLLATE database_default
11   )
12SELECT *
13FROM TestTab
14WHERE CharCol LIKE N'abc'
15
16CREATE TABLE TestTab (
17   id int, 
18   GreekCol nvarchar(10) collate greek_ci_as, 
19   LatinCol nvarchar(10) collate latin1_general_cs_as
20   )
21INSERT TestTab VALUES (1, N'A', N'a');
22
23SELECT * 
24FROM TestTab 
25WHERE GreekCol = LatinCol COLLATE greek_ci_as;
26
27SELECT (CASE WHEN id > 10 THEN GreekCol ELSE LatinCol END) COLLATE Latin1_General_CI_AS 
28FROM TestTab
29
30SELECT LatinCol COLLATE Latin1_General_CS_AS 
31FROM 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
See Also

Other Resources