Multi-statement Table Valued Function vs Inline Table Valued Function
In researching Matt's comment, I have revised my original statement. He is correct, there will be a difference in performance between an inline table valued function (ITVF) and a multi-statement table valued function (MSTVF) even if they both simply execute a SELECT statement. SQL Server will treat an ITVF somewhat like a VIEW
in that it will calculate an execution plan using the latest statistics on the tables in question. A MSTVF is equivalent to stuffing the entire contents of your SELECT statement into a table variable and then joining to that. Thus, the compiler cannot use any table statistics on the tables in the MSTVF. So, all things being equal, (which they rarely are), the ITVF will perform better than the MSTVF. In my tests, the performance difference in completion time was negligible however from a statistics standpoint, it was noticeable.
In your case, the two functions are not functionally equivalent. The MSTV function does an extra query each time it is called and, most importantly, filters on the customer id. In a large query, the optimizer would not be able to take advantage of other types of joins as it would need to call the function for each customerId passed. However, if you re-wrote your MSTV function like so:
CREATE FUNCTION MyNS.GetLastShipped()
RETURNS @CustomerOrder TABLE
(
SaleOrderID INT NOT NULL,
CustomerID INT NOT NULL,
OrderDate DATETIME NOT NULL,
OrderQty INT NOT NULL
)
AS
BEGIN
INSERT @CustomerOrder
SELECT a.SalesOrderID, a.CustomerID, a.OrderDate, b.OrderQty
FROM Sales.SalesOrderHeader a
INNER JOIN Sales.SalesOrderHeader b
ON a.SalesOrderID = b.SalesOrderID
INNER JOIN Production.Product c
ON b.ProductID = c.ProductID
WHERE a.OrderDate = (
Select Max(SH1.OrderDate)
FROM Sales.SalesOrderHeader As SH1
WHERE SH1.CustomerID = A.CustomerId
)
RETURN
END
GO
In a query, the optimizer would be able to call that function once and build a better execution plan but it still would not be better than an equivalent, non-parameterized ITVS or a VIEW
.
ITVFs should be preferred over a MSTVFs when feasible because the datatypes, nullability and collation from the columns in the table whereas you declare those properties in a multi-statement table valued function and, importantly, you will get better execution plans from the ITVF. In my experience, I have not found many circumstances where an ITVF was a better option than a VIEW but mileage may vary.
Thanks to Matt.
Addition
Since I saw this come up recently, here is an excellent analysis done by Wayne Sheffield comparing the performance difference between Inline Table Valued functions and Multi-Statement functions.
His original blog post.
Copy on SQL Server Central
When would you use a table-valued function?
Table-valued functions are "just" parameterized views. This makes them extremely powerful for encapsulating logic that would otherwise be hidden behind an opaque stored procedure. Here's an example:
Inline Table-valued Function:
create function dbo.GetClients (
@clientName nvarchar(max) = null
)
returns table
return (
select *
from dbo.Clients as a
where ((a.ClientName = @clientName) or a.ClientName is null)
);
Stored Procedure:
create procedure dbo.usp_GetClients (
@clientName nvarchar(max) = null
)
as
begin;
select *
from dbo.Clients as a
where ((a.ClientName = @clientName) or a.ClientName is null)
end;
Unlike the stored procedure call, a table-valued function allows me to compose the logic from dbo.GetClients
with other objects:
select *
from dbo.GetClients(N'ACME') as a
join ... as b
on a.ClientId = b.ClientId
In such situations I cannot imagine using a stored procedure because of how restrictive it is when compared to the table-valued function. I would be forced to marshal the data around myself using a temp table, table variable, or application layer in order to combine results from multiple objects.
Inline table-valued functions are especially awesome because of the "inline" bit which is probably best explained here. This allows the optimizer to treat such functions no differently than the objects they encapsulate, resulting in near optimal performance plans (assuming that your indexes and statistics are ideal).
Performance using Inline table valued function to encapsulate SQL code
Can inline table valued functions (ITVF) be used to encapsulate and reuse code?
Yes. And they are superior in this to multi-statement TVFs because with multi-statement TVFs the encapsulation prevents the query optimizer from pushing predicates into the TVF logic, and prevents it from accurately estimating the number of rows returned.
Or will this result in performance issues?
Short answer, not typically.
Longer answer:
There are 4 ways to encapsulate and reuse query logic (whole queries, not just scalar expressions).
- Views
- Inline Table Valued Functions
- Multi-Statement Table Valued Functions
- Temporary Tables
- Table Variables
Views and Inline TVFs don't inherently degrade performance, but they add to the complexity of query optimization.
Where the optimizer fails to consistently find low-cost plans you may need to intervene. A common way you can do that is by forcing spooling (ie materializing) of intermediate results, for instance replacing an Inline TVF with a multi-statement TVF, or by spooling results to a temp table ahead-of-time.
Spooling reduces the complexity of the encapsulating query at the cost of possible optimization of the encapsulated query when run in the context of the larger query.
When spooling results, Temp Tables are typically the best, as SQL Server they can have indexes and statistics that enable SQL Server to accurately assess the cost of the plans that will consume the intermediate results.
Table-valued Functions - Incorrect syntax near the keyword 'Begin'
You should declare your table name like below
RETURNS @Result
Then you need to insert into @Result
table instead.
Your entire code should be like this
CREATE FUNCTION [BCF].[fn_GetSpecialLastCurrencyRateByDate]
(
@SkinId INT,
@CurrencyIdFrom char(3),
@CurrencyIdTo char(3),
@EndDate datetime
)
RETURNS @Result
TABLE
(
Id INT,
CurrencyId char(3),
ToCurrencyId char(3),
Rate decimal(24,18),
Date date
)
AS
Begin
If (not exists( Select TOP 1 Id, CurrencyId,ToCurrencyId ,Rate , Date date
From BCF.SkinCurrencyRate
Where SkinId = @SkinId and CurrencyId = @CurrencyIdFrom AND ToCurrencyId = ISNULL(@CurrencyIdTo, 'EUR') and Date<=@EndDate
Order By Date desc))
BEGIN
INSERT INTO @Result
Select Id, CurrencyId,ToCurrencyId ,Rate , Date date from [BCF].fn_GetLastCurrencyRateByDate(@CurrencyIdFrom, @CurrencyIdTo, @EndDate)
END
ELSE
BEGIN
INSERT INTO @Result
Select TOP 1 Id, CurrencyId,ToCurrencyId ,Rate , Date date
From BCF.SkinCurrencyRate
Where SkinId = @SkinId and CurrencyId = @CurrencyIdFrom AND ToCurrencyId = ISNULL(@CurrencyIdTo, 'EUR') and Date<=@EndDate
Order By Date desc
END
RETURN;
end
GO
Table Valued Function with Recursive CTE
The solution is to use a multi-statement Table Valued Function:
DROP FUNCTION IF EXISTS dates;
GO
CREATE FUNCTION dates(@start date, @end date) RETURNS @dates TABLE(date date) AS
BEGIN
WITH cte AS (
SELECT @start AS date
UNION ALL
SELECT dateadd(day,1,date) FROM cte WHERE date<@end
)
INSERT INTO @dates(date)
SELECT date FROM cte OPTION(MAXRECURSION 0)
RETURN;
END;
GO
SELECT * FROM dates('2020-01-01','2021-01-01');
Inline Table Valued Functions are literally inlined, and clauses such as OPTION
can only appear at the very end of an SQL statement, which is not necessarily at the end of the inline function.
On the other hand, a Multi Statement Table Valued function is truly self-contained, so the OPTION
clause is OK there.
Related Topics
Sql: Between VS ≪= and ≫=
Importance of Varchar Length in MySQL Table
How to Make a Recursive SQL Query
SQL Select Speed Int VS Varchar
How to Concatenate Columns in a Postgres Select
Performance of Inner Join Compared to Cross Join
MySQL Not Using Indexes With Where in Clause
Update Records in Table from Cte
Stored Procedure With Optional "Where" Parameters
How to Use Join For Two Different Database Tables
Union Query With Codeigniter'S Active Record Pattern
How to Create a Step in My SQL Server Agent Job Which Will Run My Ssis Package
What Is Sysname Data Type in SQL Server
How to Use (Install) Dblink in Postgresql
Nvarchar(Max) Still Being Truncated
How to Do Pagination in SQL Server 2008