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):
- 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:
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 :-).
SQL_Latin1_General_Pref_Cp850_CI_AS_CI_WI
in my SQL instances. Do you meanSQL_Latin1_General_Pref_CP850_CI_AS
(which is Kana and width insensitive)?SQL_Latin1_General_Pref_Cp850_CI_AS
.SQL_Latin1_General_CP850_CI_AS
orSQL_Latin1_General_Pref_CP850_CI_AS
?Pref
because on MSDN page it has the same "Sort order name" asSQL_1xCompat_CP850_CI_AS
. I see in your answer that sort order should be different (it's based on SQL sort order ID ?)1xCompat
Collation and "44" for the other. SQL Server, via the query at the end of my answer, shows the sort order forSQL_Latin1_General_CP850_CI_AS
to be "42" and the sort order forSQL_Latin1_General_Pref_CP850_CI_AS
is "43".