Tech Off Thread

8 posts

Forum Read Only

This forum has been made read only by the site admins. No new threads or comments can be added.

Why does varchar and nvarchar return different resultset in search?

Back to Forum: Tech Off
  • User profile image
    gadget

    CREATE TABLE #TEST (Keyfield varchar(30) NULL)

    INSERT INTO #Test (keyfield) VALUES ('M-S Logistics');

    INSERT INTO #Test (keyfield) VALUES ('Monster Racing');

    INSERT INTO #Test (keyfield) VALUES ('Mueller Farms');

    DECLARE @Search AS nvarchar(30), @Search2 AS varchar(30)

    --Query 1

    SET @Search = 'Monster Racing'

    SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search;

    --Query 2

    SET @Search2 = 'Monster Racing'

    SELECT TOP(1) keyfield FROM #Test WHERE keyfield >= @Search2;

    -- Why does query 2 return different result than query 1

  • User profile image
    ZippyV

    What do you mean with this:

    keyfield >= @Search

  • User profile image
    PerfectPhase

    It doesn't....

  • User profile image
    gadget

    PerfectPhase wrote:
    It doesn't....


    What DBMS are you using?
    What version? 

    Using Microsoft SQL Server 2005 build 3054 it does.


    Edit: If your collation is set to binary then it doesn't.

  • User profile image
    Matthew van Eerde

    TOP is meaningless without an ORDER BY clause.

  • User profile image
    PerfectPhase

    gadget wrote:
    
    What DBMS are you using?
    What version? 

    Using Microsoft SQL Server 2005 build 3054 it does.

    Edit: If your collation is set to binary then it doesn't.


    Latin1_General_CI_AS

    SLQ-2005 SP2 3054

  • User profile image
    gadget

    Matthew van Eerde wrote:
    TOP is meaningless without an ORDER BY clause.


    Yes sorry about that, accidentally left that out of the example.

    I discovered if you use Latin1_General_Bin2 it works like you would expect. Although now it is a case sensitive search.

  • User profile image
    Antitorgo

    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?
    Ex:
    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.

Conversation locked

This conversation has been locked by the site admins. No new comments can be made.