举几个例子,以防万一:
内联表值
CREATE FUNCTION MyNS.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 MyNS.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
使用一种类型(内联或多语句)比另一种有优势吗?是否存在某些情况下一个比另一个更好,或者差异纯粹是语法上的?我意识到这两个示例查询正在做不同的事情,但我有理由这样写它们吗?
阅读它们以及它们的优点/差异并没有真正得到解释。
在研究马特的评论时,我修改了我原来的陈述。他是对的,内联表值函数 (ITVF) 和多语句表值函数 (MSTVF) 之间的性能会有差异,即使它们都只是执行 SELECT 语句。SQL Server 将 ITVF 视为有点像VIEW因为它将使用有关表的最新统计信息来计算执行计划。MSTVF 相当于将 SELECT 语句的全部内容填充到表变量中,然后加入到该变量中。因此,编译器不能对 MSTVF 中的表使用任何表统计信息。因此,在所有条件相同的情况下(它们很少如此),ITVF 将比 MSTVF 表现更好。在我的测试中,完成时间的性能差异可以忽略不计,但从统计的角度来看,这是显而易见的。
VIEW
在您的情况下,这两个功能在功能上并不等效。每次调用 MSTV 函数时都会执行一个额外的查询,最重要的是,它会根据客户 ID 进行过滤。在大型查询中,优化器将无法利用其他类型的连接,因为它需要为传递的每个 customerId 调用该函数。但是,如果您像这样重写 MSTV 函数:
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
在查询中,优化器将能够调用该函数一次并构建更好的执行计划,但它仍然不会比等效的非参数化 ITVS 或VIEW.
在可行的情况下,ITVF 应该优先于 MSTVF,因为表中列的数据类型、可空性和排序规则,而您在多语句表值函数中声明这些属性,并且重要的是,您将从 ITVF 获得更好的执行计划。根据我的经验,我没有发现很多情况下 ITVF 比 VIEW 更好,但里程可能会有所不同。