#SQL Collation – SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CI_AS

Craig Verster - Click for blog homepageToday, we have an article from guest author Craig Verster, Senior Microsoft Dynamics GP Consultant at Microchannel Services, who identified an issue that can affect Australian (and potentially other non-US) SQL Server installations.

When installing SQL Server Database Engine the default collation is dependent on the Locale of the operating system.

i.e. If Australia, then Latin1_General_CI_AS is the default collation method.

If USA (which is the default locale), then SQL_Latin1_General_CP1_CI_AS is the default collation method.

Although both these collation methods use code page 1252 they actually work very differently.

A while back I was investigating the SSRS Historical Aged Trial Balance reports and related SQL Stored procs and discovered that the default from and to string field values (to include all records from and to) were not filtering records as expected i.e. not all records were being returned in report (I hadn’t entered a specific range and left the default as is so all records should have been returned).

It turned out that the problem was around the SQL collation method. When I had installed Dynamics GP locally on my laptop I had used the default collation method which, because the locale of the OS was Australia, it used Latin1_General_CI_AS, and not the recommended sort order of SQL_Latin1_General_CP1_CI_AS by Microsoft.

Too cut a long story short, the From string field default in SSRS is the ‘Þ’ character, and this character’s sort position is different for each of the above collation methods.

For SQL_Latin1_General_CP1_CI_AS, character ‘Þ’ is the last character in code page.

For Latin1_General_CI_AS, character ‘Þ’ is not the last character in code page but comes after letters ‘t’ and ‘T’ so, if running the SSRS PM HATB report for all Vendors using defaults (‘’ to ‘ÞÞÞÞÞÞÞÞ’), Vendors beginning from S through Z will not be returned.

So to conclude, if installing new Microsoft SQL Server, make sure you select the SQL_Latin1_General_CP1_CI_AS SQL collation method rather than accepting the default Windows Collation Method.

Craig Verster is a Microsoft Certified Technology Specialist for Microsoft Dynamics GP.

He has worked as a Microsoft Dynamics GP Consultant since 1997 during which time he worked for several large Microsoft Dynamics GP Partners in South Africa, United Kingdom, United Arab Emirates, Bermuda and Australia.

This article was originally posted on http://www.winthropdc.com/blog.

2 thoughts on “#SQL Collation – SQL_Latin1_General_CP1_CI_AS vs Latin1_General_CI_AS

Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.