Weird SQL Behavior, why does this query return nothing?

Question

Suppose 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, account} be an entry in 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 returns a record, but the second does not? Why?

Systems in which these problems occur:

* Windows XP - Professional - Version 2002 - SP3
Server sorting: 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

, , , SQL Server 2005:

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

: 9.00.3054.00
: SP2
: Enterprise Edition

.. .

: SP3.

Answer 2

:

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

.

UPDATE:

, .

(0x3F).

, Management Studio .

, OS, , , SQL Server.

:

• OS (, , MUI )

  • :

    SELECT SERVERPROPERTY ('SQLCharSetName')

  • osql.exe :

    SELECT CAST ( AS BINARY),      CAST (N' 'AS BINARY),      CAST (N '% account%' AS BINARY) FROM myTable t WHERE t.ID = 4
          t.X = 1 GO

  osql.exe?

Answer 3

.

select * from myTable as t
where t.ID=4
AND t.X = 1
AND (t.Name = N'')

:

ID          X           Name
----------- ----------- ------------
4           1           

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

:

ID          X           Name
----------- ----------- ------------
4           1           

( 1 ())

SQL Server:

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)

: SQL_Latin1_General_CP1_CI_AS

Quassnoi: 0x30043A043A04300443043D04420400000000000000000000000000000000 0x30043A043A04300443043D04420400000000000000000000000000000000 0x250030043A043A04300443043D0442042500000000000000000000000000

( 1 ())