SQL Server 2019中标量用户定义函数性能的改进

In SQL Server, we normally use user-defined functions to write SQL queries. A UDF accepts parameters and returns the result as an output. We can use these UDFs in programming code and it allows writing up queries fast. We can modify a UDF independently of any other programming code.

在SQL Server中，我们通常使用用户定义的函数来编写SQL查询。 UDF接受参数并将结果作为输出返回。 我们可以在编程代码中使用这些UDF，并且可以快速编写查询。 我们可以独立于任何其他编程代码来修改UDF。

In SQL Server, we have the following types of User-Defined Functions.

在SQL Server中，我们具有以下类型的用户定义函数。

1. Scalar functions: Scalar user-defined functions return a single value. You will always have a RETURNS clause in it. The return value cannot be text, image or timestamp. Below is an example of Scalar functions. 标量函数：标量用户定义的函数返回单个值。 您将始终在其中包含RETURNS子句。 返回值不能是文本，图像或时间戳。 以下是标量函数的示例。

   Create FUNCTION dbo.ufnGetCustomerData (@CustomerID int)  
   RETURNS varchar (50)
   AS   
   BEGIN  
       DECLARE @CustomerName varchar(50);  
       SELECT @CustomerName = customername   
       FROM [WideWorldImporters].[Sales].[Customers] C
       WHERE C.CustomerID=@CustomerID
         RETURN @CustomerName;  
   END;
   

   Scalar user-defined functions are traditionally not considered a good option for high performance but SQL Server 2019 provides a way to improve performance on these scalar user-defined functions. We will learn more about it in a later section of the article.

   传统上，标量用户定义函数不被认为是高性能的好选择，但是SQL Server 2019提供了一种提高这些标量用户定义函数性能的方法。 我们将在本文的后面部分中详细了解它。

2. Multi-statement table-valued functions (TVFs): Its syntax is similar to the scalar user-defined function and provides multi-values as output. These are also not performance optimized due tocardinality estimate issues.

   多语句表值函数（TVF）：其语法类似于标量用户定义函数，并提供多值作为输出。 由于基数估计问题，这些性能也不优化。

   

   SQL Server 2012 provides fixed cardinality estimates of one row while SQL Server 2012 provides estimates to 100. SQL Server 2017 improves the cardinality estimates for these MSTVF’s using the feature called interleaved execution.

   SQL Server 2012提供固定的行基数估计，而SQL Server 2012提供的行基数估计为100。SQLServer 2017使用称为交错执行的功能改进了这些MSTVF的基数估计。

• You can learn more about this feature in the article, SQL Server 2017: Interleaved Execution for mTVF. 您可以在文章SQL Server 2017：mTVF的交错执行中了解有关此功能的更多信息。

• Inline table-valued functions: Inline table values functions are performance optimized functions. They do not contain table definitions. The query batch inside this function is a single statement, therefore, it does not provide any performance issues when we use it batches or in loops. 内联表值函数：内联表值函数是性能优化的函数。 它们不包含表定义。 此函数内部的查询批处理是一条语句，因此，当我们批量或循环使用它时，它不会提供任何性能问题。

  Below is an example of inline table valued function.

  下面是内联表值函数的示例。

  USE [WideWorldImporters]
  GO
   
  SET ANSI_NULLS ON
  GO
   
  SET QUOTED_IDENTIFIER ON
  GO
   
   
  CREATE FUNCTION [Application].[DetermineCustomerAccess](@CityID int)
  RETURNS TABLE
  WITH SCHEMABINDING
  AS
  RETURN (SELECT 1 AS AccessResult
          WHERE IS_ROLEMEMBER(N'db_owner') <> 0
          OR IS_ROLEMEMBER((SELECT sp.SalesTerritory
                            FROM [Application].Cities AS c
                            INNER JOIN [Application].StateProvinces AS sp
                            ON c.StateProvinceID = sp.StateProvinceID
                            WHERE c.CityID = @CityID) + N' Sales') <> 0
        OR (ORIGINAL_LOGIN() = N'Website'
          AND EXISTS (SELECT 1
                      FROM [Application].Cities AS c
                  INNER JOIN [Application].StateProvinces AS sp
                  ON c.StateProvinceID = sp.StateProvinceID
                  WHERE c.CityID = @CityID
                  AND sp.SalesTerritory = SESSION_CONTEXT(N'SalesTerritory'))));
  GO
  

• 标量用户定义函数 (Scalar User Defined functions)

  As stated above, a Scalar User a defined function does not provide performance benefits in SQL Server. Therefore, in this section, we will first view the performance issues with scalar user-defined function and then use SQL Server 2019 to compare performance.

  如上所述，标量用户定义的函数不会在SQL Server中提供性能优势。 因此，在本节中，我们将首先查看标量用户定义函数的性能问题，然后使用SQL Server 2019比较性能。

  For this example, I am running SQL Server 2019 2.1 and WideWorldIm