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

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)

   • What does this query return:

     SELECT SERVERPROPERTY('SQLCharSetName')

   • 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?