When I run inner join, it returns a match when one of the values is X and another is CHAR(0) + X. Is this expected?
create table t1(c nvarchar(30))
create table t2(c nvarchar(30))
insert into t1 select 'X'
insert into t2 select CHAR(0) + 'X'
select * from t1 join t2 on t1.c=t2.c
It does return the row. Also I can define a foreign key on them, and it's not violated.
But if it's varchar instead of nvarchar, then there's no result from the join, and the foreign key is violated.
I wonder if this is expected/documented behavior?