Rob Farley has a great post on when is a SQL function not a function?
Read the two below functions – they return the same thing, but one is more effecient than the other. If you can’t see why, head over to Rob’s post that shows the execution plans for a great explaination.
CREATE FUNCTION dbo.FetchSales_inline(@salespersonid int, @orderyear int)
RETURNS TABLE AS
RETURN (
SELECT e.LoginID as EmployeeLogin, o.OrderDate, o.SalesOrderID
FROM Sales.SalesOrderHeader AS o
LEFT JOIN HumanResources.Employee AS e
ON e.EmployeeID = o.SalesPersonID
WHERE o.SalesPersonID = @salespersonid
AND o.OrderDate >= DATEADD(year,@orderyear-2000,’20000101′)
AND o.OrderDate < DATEADD(year,@orderyear-2000+1,’20000101′)
)
;
GO
CREATE FUNCTION dbo.FetchSales_multi(@salespersonid int, @orderyear int)
RETURNS @results TABLE (
EmployeeLogin nvarchar(512),
OrderDate datetime,
SalesOrderID int
)
AS
BEGIN
INSERT @results (EmployeeLogin, OrderDate, SalesOrderID)
SELECT e.LoginID, o.OrderDate, o.SalesOrderID
FROM Sales.SalesOrderHeader AS o
LEFT JOIN HumanResources.Employee AS e
ON e.EmployeeID = o.SalesPersonID
WHERE o.SalesPersonID = @salespersonid
AND o.OrderDate >= DATEADD(year,@orderyear-2000,’20000101′)
AND o.OrderDate < DATEADD(year,@orderyear-2000+1,’20000101′)
;
RETURN
END
;
GO