From the article (towards the end):
The answer lies in a bug in the cluster installer for SQL2008. As I live in the UK the collation sequence chosen was Latin1_General_CI_AS which is for UK English but the installer ignores it and defaults back to SQL_Latin1_General_CP1_CI_AS which is for US English. Unless you think to check the collation sequence post build it is a very easy thing to miss.
Hi David. I'm not sure that this is a bug in the installer. More likely just a bad choice on the part of Microsoft to continue using a bad / obsolete default value. All Latin1_General and SQL_Latin1_General Collations, with the exception of 2 of the SQL_Latin1_General Collations, are for US English (those other 2 are for Turkish). There are no Collations (of either type) that are for GB English.
Also, to be clear, this issue, while it relates to VARCHAR vs NVARCHAR, is more so an issue between SQL Server Collations and Windows Collations. The problem is that VARCHAR data uses the old, simplistic sort orders when using SQL Server Collations, but uses Unicode sorting rules when using Windows Collations. The problem you experienced is that the VARCHAR column was using a SQL Server Collation and the rows were ordered in a particular way according to that sort order. But, due to datatype precedence, when compared with an NVARCHAR variable or string literal, the column values (sourced from the index) were converted to NVARCHAR. And, since the NVARCHAR sorting rules produce a different ordering than the static sort order of the SQL Server Collation, the physical order of the rows in the index is no longer correct.
Because VARCHAR columns using Windows Collations use the same Unicode sorting rules as NVARCHAR data for that same Collation, implicitly converting to NVARCHAR is a non-issue as the physical order of the rows in the index won't change, even if the data changes from an 8-bit encoding to UTF-16 LE. This is why you will still see a CONVERT_IMPLICIT() when comparing a VARCHAR column (using a Windows Collation) to an NVARCHAR variable or string literal, because the conversion still has to happen. But, being in the same / correct physical order upon being converted means that the index is still completely valid, hence you still get a SEEK instead of a SCAN :-). I have a post specifically about this behavior:
Impact on Indexes When Mixing VARCHAR and NVARCHAR Types
Also, as it relates to changing the Collation of the Instance / system DBs, etc. I recently posted about the quick-and-mostly-easy (though undocumented / unsupported) method here:
Changing the Collation of the Instance, the Databases, and All Columns in All User Databases: What Could Possibly Go Wrong?