SA0161 : Current database uses old SQL Server collation. To take full advantage of SQL Server features, for new development change the default installation settings to use Windows collations

Using SQL Server Collations can cause various issues in In T-SQL code and SQL Server environments. Although they are kept for backward compatibility, it is advisable to use Windows Collations instead. SQL Server Collations often lead to mismatches in data types and performance problems, especially due to implicit conversions. This problem is rooted in the default installation settings that come with SQL Server, which often use a SQL Server Collation such as SQL_Latin1_General_CP1_CI_AS.

For example:

1-- Example of a query with potential collation issues
2SELECT * FROM TableName WHERE ColumnName = 'test';

In the above example, performance can be affected if the database collation causes implicit conversions, as SQL Server may need to change data types to compare values properly. This can slow query performance and lead to unexpected results.

• Implicit conversions may degrade query performance.

• Unexpected behavior due to collation mismatches between databases or inputs.

This guidance explains how to resolve issues related to using SQL Server Collations and instead recommends adopting Windows Collations for better performance and compatibility.

Follow these steps to address the issue:

1. Determine the current collation setting of your SQL Server by running the following query: SELECT SERVERPROPERTY(‘Collation’).

2. If the server is using a SQL Server Collation, plan for a collation change. This may involve creating a new database with a Windows Collation. Use CREATE DATABASE DatabaseName COLLATE NewWindowsCollationName for this purpose.

3. Update existing databases to use a Windows Collation by setting the database collation using: ALTER DATABASE DatabaseName COLLATE NewWindowsCollationName.

4. Ensure individual columns that store string data types are set to use Windows Collations. Use: ALTER TABLE TableName ALTER COLUMN ColumnName TYPE VARCHAR(50) COLLATE NewWindowsCollationName for each column, as required.

5. Review and update any T-SQL queries that may involve collation-dependent operations to ensure they perform consistently with the new collations.

For example:

1-- Example of creating a new database with a Windows Collation
2CREATE DATABASE MyNewDatabase COLLATE Latin1_General_100_CI_AS;
3
4-- Example of updating a column's collation
5ALTER TABLE Customers ALTER COLUMN LastName VARCHAR(50) COLLATE Latin1_General_100_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 Analysis Context. If context is missing, the rule will be skipped during analysis.

Design Rules, Deprecated Features, Bugs

SQL Server Collation Name (Transact-SQL)

Other Resources