Weird SQL Behavior, why is this query returning nothing?

Question

Assume there is a table named "myTable" with three columns:

{**ID**(PK, int, not null), 
 **X**(PK, int, not null), 
 **Name**(nvarchar(256), not null)}.

Let {4, 1, аккаунт} be a record on the table.

select * from myTable as t 
    where t.ID=4  
    AND t.X = 1 
    AND (     t.Name = N'аккаунт'  )

select * from myTable as t 
    where t.ID=4  
    AND t.X = 1 
    AND (  t.Name LIKE N'%аккаунт%'  )

The first query return the record, however, the second does not? Why?

Systems where this issues are experienced:

*Windows XP - Professional - Version 2002 - SP3
Server Collation: Latin1_General_CI_AS
Version: 9.00.3073.00
Level: SP2
Edition: Developer Edition

Sever Collation: SQL_Latin1_General_CP1_CI_AS
Version: 9.00.3054.00
Level: SP2
Edition: Enterprise Edition

Results:

SELECT SERVERPROPERTY('SQLCharSetName')
iso_1

Using OSQL.exe
0x30043A043A04300443043D04420400000000000000000000000000000000
0x3F3F3F3F3F3F3F0000000000000000000000000000000000000000000000
0x253F3F3F3F3F3F3F25000000000000000000000000000000000000000000

SELECT CAST(name AS BINARY),
       CAST(N'аккаунт' AS BINARY),
       CAST(N'%аккаунт%' AS BINARY)
FROM   myTable t
WHERE  t.ID = 4  
       AND t.X = 1

CAST(name AS BINARY) 
0x30043A043A04300443043D04420400000000000000000000000000000000  
CAST(N'аккаунт' AS BINARY)
0x3F3F3F3F3F3F3F0000000000000000000000000000000000000000000000  
CAST(N'%аккаунт%' AS BINARY)
0x253F3F3F3F3F3F3F25000000000000000000000000000000000000000000

Answer 1

Both queries return the same result for me.

select * from myTable as t
where t.ID=4
AND t.X = 1
AND (t.Name = N'аккаунт')

Returns:

ID          X           Name
----------- ----------- ------------
4           1           аккаунт

And

select * from myTable as t
where t.ID=4
AND t.X = 1
AND (t.Name LIKE N'%аккаунт%')

Returns:

ID          X           Name
----------- ----------- ------------
4           1           аккаунт

(1 row(s) affected)

My version of SQL Server is:

Microsoft SQL Server 2005 - 9.00.3077.00 (Intel X86) 
    Dec 17 2008 15:19:45 
    Copyright (c) 1988-2005 Microsoft Corporation
    Express Edition on Windows NT 5.1 (Build 2600: Service Pack 3)

My collation is set to: SQL_Latin1_General_CP1_CI_AS

My results for Quassnoi: 0x30043A043A04300443043D04420400000000000000000000000000000000 0x30043A043A04300443043D04420400000000000000000000000000000000 0x250030043A043A04300443043D0442042500000000000000000000000000

(1 row(s) affected)

Answer 2

Could you please post the result of the following query:

SELECT CAST(name AS BINARY),
       CAST(N'аккаунт' AS BINARY),
       CAST(N'%аккаунт%' AS BINARY)
FROM   myTable t
WHERE  t.ID = 4  
       AND t.X = 1

This will help to narrow the problem down.

UPDATE:

As I can see from the results of your query, you have a problem with encoding.

The Cyrillic literals from your string constants are being converted to the question marks (0x3F).

Unfortunately, I cannot reproduce this behavior with Management Studio on my test server.

I reckon there is some problem with OS settings, as Cyrillic characters most probably don't even reach SQL Server.

Could you please answer three more questions:

1. What OS are you using (version, language, MUI if any)

2. What does this query return:

   SELECT SERVERPROPERTY('SQLCharSetName')
   

3. Connect to your server using osql.exe and issue this query:

   SELECT CAST(name AS BINARY),
          CAST(N'аккаунт' AS BINARY),
          CAST(N'%аккаунт%' AS BINARY)
   FROM   myTable t
   WHERE  t.ID = 4  
          AND t.X = 1
   GO
   

   What does it return being run in osql.exe?

Answer 3

Alright, after a great deal of research, I found it is indeed a problem found on the following versions of SQL Server 2005:

Windows XP - Professional - Version 2002 - SP3
Version: 9.00.3073.00
Level: SP2
Edition: Developer Edition

Version: 9.00.3054.00
Level: SP2
Edition: Enterprise Edition

..may be other versions as well.

FIX: Upgrade to SP3.