Home > Database > Mysql Tutorial > Inline vs. Multi-Statement Table-Valued Functions in SQL Server: Which Should You Choose?

Inline vs. Multi-Statement Table-Valued Functions in SQL Server: Which Should You Choose?

Mary-Kate Olsen
Release: 2025-01-12 19:12:42
Original
677 people have browsed it

Inline vs. Multi-Statement Table-Valued Functions in SQL Server: Which Should You Choose?

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>
Copy after login

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!

source:php.cn
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
Latest Articles by Author
Popular Tutorials
More>
Latest Downloads
More>
Web Effects
Website Source Code
Website Materials
Front End Template