SQL Server Table Function Performance

Friday, November 18 2011 @ 08:34 PM EST

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

Comments (0)

Julian Kuiters
http://www.julian-kuiters.id.au/article.php/sql-table-function-performance