SQL Server | String Comparison

I am facing some strange issue and want to understand the reason behind this.

We have two database servers Say A and B. On both of these servers we have our applicati开发者_如何学Goon database (Same schema but different records)

Problem : We have a SqlQuery

Select * from Person where memberId=123456

This query runs perfectly and return the rows selected on server - A. But the same query on a different server-B doesnt return any records.

But if i modify my query to

Select * from Person where memberId='123456' 

( notice the single quotes)

Now it returns me proper records.

DataType of memberId is nchar(100) . Technically i understand that i should compare it using the single quotes.

But just want to understand why is this happening??

Update : 1) Both have exactly the same schema. 2) Both have same records

Actual Code :

Actually this query is a dynamically created and then executed using

declare @sql varchar(2000)
    set @sql = 'SELECT * FROM PersonTrivia where memberId='+ @MemberId
    print @sql 
    exec (@sql)

and this parameter @MemberId is varchar(250)

Does the query return no records or it gives you an error?

It looks like you can enter numbers into a nchar field, however, the first time you add a character you won't be able to query for "integers" anymore... or at least it seems so.

CREATE TABLE [dbo].[testnchar](
    [id] [nchar](10) NULL,
    [name] [nchar](100) NULL
)
GO

insert testnchar
select 1, 222222

select * from testnchar 
where name = 222222

id         name
---------  --------
1          222222 

insert testnchar
select 1, 'test'

select * from testnchar 
where name = 222222

--Msg 245, Level 16, State 1, Line 1
--Conversion failed when converting the nvarchar value 'test       

delete testnchar 
where name = 'test'

select * from testnchar 
where name = 222222

id         name
---------  --------
1          222222 

I will be very interested to know if you come up with another reason for why this is occurring, but I am starting to doubt it. I am fairly certain you are catching or suppressing an error somewhere.

Consider the following:

CREATE TABLE [Person]
(
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [MemberID] [nchar](200) NULL,
    [Data] [varchar](50) NULL,
    CONSTRAINT [PK_Person] PRIMARY KEY CLUSTERED 
    (
        [ID] ASC
    )
)

INSERT Person([MemberID],[Data]) VALUES ('1111111111', 'Test1');
INSERT Person([MemberID],[Data]) VALUES ('2222222222', 'Test2');
INSERT Person([MemberID],[Data]) VALUES ('3333333333', 'Test3');
INSERT Person([MemberID],[Data]) VALUES ('NON-NUMERIC', 'Test4');

SELECT * FROM Person WHERE MemberID = 2222222222

The query above will return (1) result AND an error. So, if your code looked like the following:

command = new SqlCommand( 
     @"SELECT * FROM Person WHERE MemberID = 2222222222", connection );
try
{
    reader = command.ExecuteReader();
    while ( reader.Read() )
    {
        Console.WriteLine( "MemberID = " + reader["MemberID"] );
    }
    // We'll never get here.
    reader.Close();
}
catch { }

The result you would get would be MemberID = 2222222222. This could also occur if you were using SQL TRY...CATCH blocks. However, if we change the order¹ of the records:

TRUNCATE TABLE [Person]

INSERT Person([MemberID],[Data]) VALUES ('NON-NUMERIC', 'Test1');
INSERT Person([MemberID],[Data]) VALUES ('1111111111', 'Test2');
INSERT Person([MemberID],[Data]) VALUES ('2222222222', 'Test3');
INSERT Person([MemberID],[Data]) VALUES ('3333333333', 'Test4');

You will essentially get (0) results before the exception is thrown. Finally, if you changed your query to:

SELECT T.* FROM
(
    SELECT TOP 100 *
    FROM Person
    ORDER BY MemberID
) T
WHERE T.MemberID = 2222222222

... you'd get (1) record and an error.

My advice would be to find out if and why you are suppressing an error. My overall advice is to not compare a character field to an integer and rely on an implicit conversion.

^{1. Clustered indexes don't guarantee row order. It almost certainly will in this test, but it was worth pointing out.}

Obviously memberId is converted to int before comparison (I assume there is no errors in query exec on 2nd machine?). So my first guess would be that this is machine of Sql Server culture specific, i.e. memberId can be converted to int on first machine and can't on other. Or, since records differ (?) there is just some "wrong" records on 2nd machine. However last thing should result in runtime error.