USE Credit
go

CREATE FUNCTION dbo.MemberName
	(@MemberNo	int)
RETURNS varchar(31)
AS
BEGIN
RETURN
	(SELECT m.FirstName + ' ' + m.LastName
	FROM dbo.Member AS m
	WHERE m.member_no = @MemberNo)
END
GO

SELECT m.member_no
 , m.FirstName + ' ' + m.LastName
FROM dbo.member AS m

SELECT m.member_no
 , dbo.MemberName(m.member_no)
FROM dbo.member AS m

-- expression in select list with a join
-- with no trace, no showplan
-- 00:00:14, 00:00:02
SELECT c.member_no, m.FirstName + ' ' + m.LastName, sum(c.charge_amt)
FROM dbo.charge AS c
	JOIN dbo.member AS m ON c.member_no = m.member_no
GROUP BY c.member_no, m.FirstName + ' ' + m.LastName

-- 00:00:51, 00:05:11, 00:01:20
SELECT c.member_no, dbo.MemberName(c.member_no), sum(c.charge_amt)
FROM dbo.charge AS c
GROUP BY c.member_no, dbo.MemberName(c.member_no)
