Tech Off Post

Single Post Permalink

View Thread: Why does varchar and nvarchar return different resultset in search?
  • User profile image

    Any reason why you are using that collation? If you are stuck with that DB collation, why not add a COLLATE clause to the query?
    SELECT TOP(10) keyfield FROM #Test WHERE keyfield >= @Search2 COLLATE Latin1_General_CI_AI;


    The reason why is found in BoL:

    In SQL Server 2005, you should primarily use Windows collations. This is particularly true if you have a mix of Unicode and non-Unicode columns in your database. Windows collations actually apply Unicode-based sorting rules to both Unicode and non-Unicode data. This means that SQL Server internally converts non-Unicode data to Unicode to perform comparison operations. This provides consistency across data types in SQL Server and also provides developers with the ability to sort strings in their applications that use the same rules that SQL Server uses.

    SQL collations, on the other hand, apply non-Unicode sorting rules to non-Unicode data, and Unicode sorting rules to Unicode data, by using a corresponding Windows collation for the Unicode data. This difference can cause inconsistent results for comparisons of the same characters. Therefore, if you have a mix of Unicode and non-Unicode columns in your database, they should all be defined by using Windows collations so that the same sorting rules are used across Unicode and non-Unicode data.

    You should use SQL collations only to maintain compatibility with existing instances of earlier versions of SQL Server or to maintain compatibility in applications that were developed by using SQL collations in earlier versions of SQL Server.


    Also, Latin1_General_Bin has specific rules due to legacy behavior which is why Latin1_General_Bin2 was introduced.