SQL Server table-valued functions: Comparison of inline functions and multi-statement functions
SQL Server’s table-valued functions (TVFs) allow multiple rows of data to be returned from a single function call. However, TVFs are mainly divided into two types: multi-statement functions and inline functions.
Inline Table Valued Function (ITVF)
ITVF is defined using a single SELECT statement within the function definition. They behave like views, inheriting the data type, nullability, and collation of the referenced columns.
Multiple Statement Table Valued Function (MSTVF)
MSTVF is defined using multiple statements, usually including an INSERT statement to populate table variables. They require explicit declaration of the column's data type and properties in the function definition.
Performance Difference
Despite the different syntax, the performance difference between ITVF and MSTVF is significant. ITVF takes full advantage of SQL Server's table statistics-based query optimizer to generate efficient execution plans. MSTVF is more like a table variable, the query optimizer has poor optimization effect, and the execution plan efficiency is relatively low.
Applicable scenarios
The choice of ITVF and MSTVF depends on the specific application scenario. If the function logic can be expressed in a simple SELECT statement, ITVF is usually more efficient and recommended. MSTVF is more suitable when additional processing or parameterization is required.
Example
Consider the following example:
<code class="language-sql">-- 内联表值函数 CREATE FUNCTION GetUnshippedOrders() RETURNS TABLE AS RETURN SELECT a.SaleId, a.CustomerID, b.Qty FROM Sales.Sales a INNER JOIN Sales.SaleDetail b ON a.SaleId = b.SaleId INNER JOIN Production.Product c ON b.ProductID = c.ProductID WHERE a.ShipDate IS NULL GO -- 多语句表值函数 CREATE FUNCTION GetLastShipped(@CustomerID INT) RETURNS @CustomerOrder TABLE (SaleOrderID INT NOT NULL, CustomerID INT NOT NULL, OrderDate DATETIME NOT NULL, OrderQty INT NOT NULL) AS BEGIN DECLARE @MaxDate DATETIME SELECT @MaxDate = MAX(OrderDate) FROM Sales.SalesOrderHeader WHERE CustomerID = @CustomerID 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 = @MaxDate AND a.CustomerID = @CustomerID RETURN END GO</code>
In this example, MSTVF is used to calculate the last shipped order for a specific customer, which requires additional processing. ITVF only needs to retrieve the list of all unshipped orders, which is more suitable for simple filtering operations.
The above is the detailed content of Inline vs. Multi-Statement Table-Valued Functions in SQL Server: Which Should You Choose?. For more information, please follow other related articles on the PHP Chinese website!