SQL Server | String Comparison

Question

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 application 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)

Answer 1

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