TVF 的应用和比较

原来的sql:(inner join)

select p.title,p.postid,p.CategoryID,m.displayname,m.login_Email,m.memberid from member m inner join  post p
on p.memberid=m.memberid where m.memberid=0

使用TVF:(cross apply)

if exists(select * from sysobjects where type='IF' and name='ReTurnPostTVF')
begin
drop function ReTurnPostTVF
end
create function ReTurnPostTVF
(
@memberid int
)
returns Table
as return
select p.title,p.postid from post p where p.memberid=@memberid
Go
select p.title,p.postid,m.displayname,m.login_Email,m.memberid from member m
cross apply  ReTurnPostTVF(m.memberid) p where m.memberid=0 

OUTER APPLY   相当　left join