Can old collation type impact database performance? 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: 
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?
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] &lt;&gt; lat.[Value]
OR     compat.[Value] &lt;&gt; pref.[Value]
OR     lat.[Value] &lt;&gt; 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 :-).