Upsizing and SQL Server Collation Codes

Access applications are not case-sensitive. However, if you upsize to a SQL Server which has been configured as case sensitive you may need to take a deeper look at this issue, this article discusses when this can occur and some strategies for dealing with the problem. This is an unusual problem and not one that most people face; however, using certain popular third party products in your SQL Server instance notably SAGE accounts may mean you have to give this setting further consideration.

Collation Codes

To understand what is meant by a collation code, the best reference is the SQL Server help system. Below is an extract from the help system which describes what is meant by the collation code.

“Collations in SQL Server provide sorting rules, case, and accent sensitivity properties for your data. Collations that are used with character data types such as char and varchar dictate the code page and corresponding characters that can be represented for that data type. Whether you are installing a new instance of SQL Server, restoring a database backup, or connecting server to client databases, it is important that you understand the locale requirements, sorting order, and case and accent sensitivity of the data you will be working with.“

Top of Page

Server Collation Codes

If you don’t not review or change the collation code when setting up a SQL Server, then SQL Server will make the decision for you as part of the setup. A good source of information for the decisions SQL Server takes in establishing its selection for your collation code is the following :-

http://msdn.microsoft.com/en-us/library/ms144260.aspx

More recent versions of SQL Server will default to a Latin_General_CI_AS collation code. This is a case insensitive collation code and ideally suited to upsizing Access applications. A simple way to find out your servers collation code is to create a new database and then as directed below examine the database properties, alternatively system tables can be interrogated (but this is not detailed here as it can vary between different server versions).

When a new database is created, it gets assigned the collation code associated with the system database model. So if you have permissions on the model database you can simply view the properties of this database to establish the default collation when creating new databases, without actual creating a new database.

Top of Page

Database Collation Code

If you look at the database properties, by right-clicking on the database you can see the collation.

Top of Page

Changing a Database Collation Code

Using the above graphical interface dialog the database collation can be viewed and changed. Alternatively you can use the ALTER DATABASE syntax to change a collation code (see SQL Server help system for more details on this.)

Top of Page

Changing a Server Collation Code

The Server collation code can not be easily changed without rebuilding the system databases, it is set as part of the installation sequence and fixed for the system databases.

Top of Page

Query Translation Gotcha and Collation Codes

We have had a few customers who have come across the following rather nasty side-effect of translating SQL into a SQL Server database that has been used with a collation code of Latin1_General_BIN. This applies mainly to applications with complex calculations in the SQL.

Consider a query which has a calculated column called CostLevel, and then another calculated column either in the same query, or in a different query, which refers to this column inside another calculation, but refers to the column as costlevel. This is easily encountered in Access, as it would not automatically correct the fieldname reference from costlevel to CostLevel. This query will fail to translate in this case, because in the selected collation the field costlevel is unknown.

The solution in this case would be to create your database with a non-case sensitive collation code, such as the default collation code Latin1_General_CI_AS. But in cases where you are using a third-party product which may have demanded a Server default case sensitive collation code, read the rest of this article.

Top of Page

SAGE Line 500

SQL Servers that have been configured for SAGE software; as part of the installation process will have the server configured for a Latin1_General_BIN collation code. This means that the system databases and of particular importance TEMPDB will be configured with the Latin1_General_BIN collation code.

There are several implications from this, firstly by default when you create a new database it will be assigned a Latin1_General_BIN collation code, and for reasons outlined earlier in this article you may prefer a different collation code, but this is easily changed for your database. The more subtle issue is that the collation code of TEMPDB is also set to the case sensitive collation code, and this can not be changed. This means that when joining or processing against temporary objects in TEMPDB you may be still affected by the different collation code.

The options here are to either live with this fact and adjust your processing accordingly, or to install a separate SQL Server instance for any application which requires a different collation code which will then have TEMPDB equipped with your chosen collation code.

Top of Page

COLLATE predicate

If you have to live in an environment with mixed collation codes, then you may want to investigate the use of the COLLATE predicate to resolve these issue.

Acknowledgments

Our thanks to our customers Gillian MacKenzie, and Steve O'Connor for having drawn our attention to these issues and provided feedback to allows us to more deeply probe the consequences of collation codes.

Top of Page