Interleaved execution

Interleaved execution is a feature introduced with SQL Server 2017 and, in its current release, only supports multistatement table-valued functions. A well-known problem with multistatement table-valued functions is that they do not provide a cardinality estimate, which, in some cases, can create a performance problem as the query optimizer might not have good enough information to produce an efficient plan. With interleaved execution, SQL Server will pause query optimization, execute the multistatement table-valued function, and use an actual and accurate row count, to continue the query optimization.

Let’s test the feature using the following multistatement table-valued function:

CREATE FUNCTION dbo.tvf_Sales(@year int)

RETURNS @Sales TABLE (

 SalesOrderID int,

 SalesOrderDetailID int,

 CarrierTrackingNumber nvarchar(25),

 OrderQty smallint,

 ProductID int,

 SpecialOfferID int,

 UnitPrice money,

 UnitPriceDiscount money...