5

In my project we use SQL_1xCompat_CP850_CI_AS as collation setting for SQL Server instance and for our application's databases. As far I know this is one of very old collations still supported just for backward compatibility purposes. I would like to know whether usage of such "old" collation type may impact overall SQL Server performance?

In addition, is there any difference between SQL_1xCompat_CP850_CI_AS and SQL_Latin1_General_Pref_Cp850_CI_AScollation? Description on MSDN looks very similar: enter image description here Both are CI_AS_KI_WI, use code page 850, and have the same sort order name nocase34.850. Will we have any benefits if we will change our collation setting to SQL_Latin1_General_Pref_Cp850_CI_AS?

6
  • I don't see collation SQL_Latin1_General_Pref_Cp850_CI_AS_CI_WI in my SQL instances. Do you mean SQL_Latin1_General_Pref_CP850_CI_AS (which is Kana and width insensitive)?
    – Dan Guzman
    May 28, 2017 at 15:57
  • @DanGuzman Apologize for confusion. I took this collation name from linked MSDN blog post. Yes, I mean SQL_Latin1_General_Pref_Cp850_CI_AS. May 28, 2017 at 16:21
  • @MarekMasko Are you sure you are wanting the "Pref" Collation or non-Pref? SQL_Latin1_General_CP850_CI_AS or SQL_Latin1_General_Pref_CP850_CI_AS? May 28, 2017 at 16:24
  • @srutzky I asked about Pref because on MSDN page it has the same "Sort order name" as SQL_1xCompat_CP850_CI_AS. I see in your answer that sort order should be different (it's based on SQL sort order ID ?) May 28, 2017 at 16:32
  • 1
    @MarekMasko I think the documentation has some errors in it, but even in what you posted in the question it shows "49" for your current 1xCompat Collation and "44" for the other. SQL Server, via the query at the end of my answer, shows the sort order for SQL_Latin1_General_CP850_CI_AS to be "42" and the sort order for SQL_Latin1_General_Pref_CP850_CI_AS is "43". May 28, 2017 at 16:39

1 Answer 1

9

I would like to know whether usage of such "old" collation type may impact overall SQL Server performance?

Well, there shouldn't be a performance difference between SQL_1xCompat_CP850_CI_AS and SQL_Latin1_General_Pref_CP850_CI_AS. Both are the old, simple sort order lists and don't take into account any of the Unicode linguistic rules which the newer Windows Collations (those not starting with SQL_) can do (even for VARCHAR data).

There probably is a slight performance decrease, in typical situations, for using the newer Windows Collations as they are not just simple lists but do tap into the Unicode linguistic rules. However, whatever performance degradation there may be is worth it due to the far better functionality.

One area where there is a performance gain is when comparing NVARCHAR data to an indexed VARCHAR column using a SQL Server Collation (one starting with SQL_) as a conversion needs to take place. An indexed VARCHAR column using a Windows Collation does not get this hit when compared to NVARCHAR data. But this is more of a technical note and not something that should be expected to be an issue for most queries / scenarios.

Is there any difference between SQL_1xCompat_CP850_CI_AS and SQL_Latin1_General_Pref_CP850_CI_AS?

As denoted in the documentation presented in the question: yes. The sort order is different between them. That should be the only difference as the Code Page and sensitivities are the same between them.

Please note that there are two mistakes in that documentation (but these differences do not change the fact that there are definitely two different sort orders between these two Collations):

  1. Sort Order ID for SQL_Latin1_General_Pref_CP850_CI_AS is 43, not 44
  2. Sort Order Name cannot be the same between both listed Collations as they are different IDs (even if one of the listed IDs is incorrect, the correct ID is still different).

To see the difference please try the following test:

SETUP

CREATE TABLE #CollationTest
(
  [Value] TINYINT NOT NULL PRIMARY KEY,
  [1xCompat] VARCHAR(10) COLLATE SQL_1xCompat_CP850_CI_AS NOT NULL,
  [Latin1] VARCHAR(10) COLLATE SQL_Latin1_General_CP850_CI_AS NOT NULL,
  [Latin1Pref] VARCHAR(10) COLLATE SQL_Latin1_General_Pref_CP850_CI_AS NOT NULL
);

;WITH nums AS
(
  SELECT TOP (256) (ROW_NUMBER() OVER (ORDER BY (SELECT 0)) - 1) AS [num]
  FROM   [master].[sys].[all_columns] ac
)
INSERT INTO #CollationTest ([Value], [1xCompat], [Latin1], [Latin1Pref])
  SELECT [num], CONVERT(VARBINARY(1), [num]), CONVERT(VARBINARY(1), [num]),
         CONVERT(VARBINARY(1), [num])
  FROM nums
  ORDER BY [num] ASC;

TEST 1: Verify that all characters are the same

SELECT * FROM #CollationTest;

TEST 2: Compare the sort ordering between them

;WITH compat AS
(
  SELECT ct.[Value], ct.[1xCompat],
         ROW_NUMBER() OVER (ORDER BY ct.[1xCompat] ASC) AS [ord]
  FROM   #CollationTest ct
), lat AS
(
  SELECT ct.[Value], ct.[Latin1],
         ROW_NUMBER() OVER (ORDER BY ct.[Latin1] ASC) AS [ord]
  FROM   #CollationTest ct
), pref AS
(
  SELECT ct.[Value], ct.[Latin1Pref],
         ROW_NUMBER() OVER (ORDER BY ct.[Latin1Pref] ASC) AS [ord]
  FROM   #CollationTest ct
)
SELECT compat.[ord], compat.[1xCompat], lat.[Latin1], pref.[Latin1Pref]
FROM   compat
INNER JOIN lat
        ON lat.ord = compat.ord
INNER JOIN pref
        ON pref.ord = compat.ord
WHERE  compat.[Value] <> lat.[Value]
OR     compat.[Value] <> pref.[Value]
OR     lat.[Value] <> pref.[Value]
ORDER BY compat.[ord] ASC;

The differences start at row / value 49, and the WHERE clause filters out the first 48 rows where they all match (boring stuff).

It is fairly obvious that there are numerous differences in the sort ordering. One of those differences is that with SQL_1xCompat_CP850_CI_AS the 52 non-accented characters are grouped together with the upper-case always being first, while the accented characters are not only spread out among the rest, but the upper-case and lower-case versions of the same letter aren't even kept together:

m, N, n, O

On the other hand, with SQL_Latin1_General_Pref_CP850_CI_AS both the non-accented and accented characters are all together, grouped by accent:

m, N, n, Ñ, ñ, O

Will we have any benefits if we will change our collation setting to SQL_Latin1_General_Pref_CP850_CI_AS?

Most likely the only real benefit is probably longevity. I would expect Microsoft to get rid of SQL_1xCompat_CP850_CI_AS at some point.

You should test moving to SQL_Latin1_General_Pref_CP850_CI_AS and if the difference in sort order doesn't adversely affect the app, then probably a good idea to do.

It is probably even better to move to a Windows Collation, but that involves more testing as there are a few other functional differences. Unfortunately, using the following I found that none of the Windows Collations use Code Page 850:

SELECT *
FROM sys.fn_helpcollations()
WHERE  COLLATIONPROPERTY([name], 'CodePage') = 850;

So, while it is still preferred to be using a Windows Collation, changing from your existing Code Page 850 Collation to a newer one (most likely using Code Page 1252) is a much larger testing effort. IF you have the time and resources I say go for it, else just do the change you were asking about :-).

6
  • There is a difference in sort order between these 2 collations. Collation SQL_1xCompat_CP850_CI_AS uses sort order 49 whereas SQL_Latin1_General_CP850_CI_AS uses sort order 43. I'll add an answer with a demo script that illustrates the difference.
    – Dan Guzman
    May 28, 2017 at 16:21
  • @DanGuzman Yes, I specifically stated that the sort order was THE difference. May 28, 2017 at 16:23
  • Sorry I didn't see that you had mentioned that as I was multi-tasking on the script:-) I agree that only knowledge and testing of the app can determine whether the differences in sorting and comparison behavior will be a breaking change.
    – Dan Guzman
    May 28, 2017 at 16:27
  • 1
    The issue with indexed varchar columns and implicit casts is not that rare. AddWithValue will create an nvarchar parameter. I've seen this quite a few times on SO e.g. stackoverflow.com/a/7638059/73226 May 28, 2017 at 16:43
  • @DanGuzman Understood. Been there myself ;-). I have updated with the test code, if you're interested. May 28, 2017 at 19:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.