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_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
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
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):
- Sort Order ID for
SQL_Latin1_General_Pref_CP850_CI_AS is 43, not 44
- 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:
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]),
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]
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:
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 :-).