use pubs
SET ANSI_NULLS ON
SET ARITHABORT ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
if object_id('b') is not null drop table b
if object_id('a') is not null drop table a
go
create table a
(
aidnotnull int not null,
aidnull int null,
aname varchar(50),
computedaidnull as isnull(aidnull, 0),
constraint pka unique (aidnotnull, aidnull)
)
create table b
(
bid int primary key,
bname varchar(50),
aidnotnull int not null,
aidnull int null,
computedaidnull as isnull(aidnull, 0),
constraint fka foreign key (aidnotnull, aidnull) references a(aidnotnull, aidnull)
)
create index ndxaforcomputed on a(aidnotnull, computedaidnull)
create index ndxbforcomputed on b(aidnotnull, computedaidnull)
go
insert into a select 1, 1, 'a without null'
insert into a select 1, NULL, 'a with null'
insert into b select 2, 'b to a without null', 1, 1
insert into b select 3, 'b to a with null', 1, NULL
update statistics a
update statistics b
go
select * from b left join a on (b.aidnotnull = a.aidnotnull and b.aidnull = a.aidnull)
select * from b left join a on (b.aidnotnull = a.aidnotnull and b.computedaidnull = a.computedaidnull)
select * from b left join a on (b.aidnotnull = a.aidnotnull and isnull(b.aidnull, 0) = isnull(a.aidnull, 0))
go
drop table b
drop table a
go
|