SQL server中的标量、表值和聚合函数之间的区别?

What is the difference between scalar-valued, table-valued, and aggregate functions in SQL server? And does calling them from a query need a different method, or do we call them in the same way?

SQL server中标量值、表值和聚合函数之间的区别是什么?从查询中调用它们需要不同的方法吗?还是用相同的方式调用它们?

3 个解决方案

#1

Scalar Functions

Scalar functions (sometimes referred to as User-Defined Functions / UDFs) return a single value as a return value, not as a result set, and can be used in most places within a query or SET statement, except for the FROM clause (and maybe other places?). Also, scalar functions can be called via EXEC, just like Stored Procedures, though there are not many occasions to make use of this ability (for more details on this ability, please see my answer to the following question on DBA.StackExchange: Why scalar valued functions need execute permission rather than select?). These can be created in both T-SQL and SQLCLR.

标量函数(有时称为用户定义函数/ udf)返回单个值作为返回值，而不是结果集，除了FROM子句(或者其他地方?)之外，在查询或set语句中的大多数地方都可以使用标量函数。同样，标量函数也可以通过EXEC调用，就像存储过程一样，尽管使用此功能的场合并不多(关于此功能的详细信息，请参阅我对DBA以下问题的回答。StackExchange:为什么标量值函数需要执行权限而不是选择?这些都可以在T-SQL和SQLCLR中创建。

• T-SQL (UDF): these scalar functions are typically a performance issue because they generally run for every row returned (or scanned) and always prohibit parallel execution plans.
• T-SQL (UDF):这些标量函数通常是性能问题，因为它们通常针对返回(或扫描)的每一行运行，并且总是禁止并行执行计划。

• SQLCLR (UDF): these scalar functions also typically run per each row returned or scanned, but there are two important benefits over T-SQL UDFs:

  SQLCLR (UDF):这些标量函数通常在返回或扫描的每一行中运行，但是与T-SQL UDF相比有两个重要的优点:

  • Starting in SQL Server 2012, return values can be constant-folded into the execution plan IF the UDF does not do any data access, and if it is marked IsDeterministic = true. In this case the function wouldn't run per each row.
  • 从SQL Server 2012开始，如果UDF不进行任何数据访问，并且标记为IsDeterministic = true，那么返回值可以被固定地折叠到执行计划中。在这种情况下，函数不会每一行运行。
  • SQLCLR scalar functions can work in parallel plans ( ???? ) if they do not do any database access.
  • SQLCLR标量函数可以并行工作计划(????)如果他们不做任何数据库access.

Table-Valued Functions

Table-Valued Functions (TVFs) return result sets, and can be used in a FROM clause, JOIN, or CROSS APPLY / OUTER APPLY of any query, but unlike simple Views, cannot be the target of any DML statements (INSERT / UPDATE / DELETE). These can also be created in both T-SQL and SQLCLR.

表值函数(TVFs)返回结果集，可用于任何查询的FROM子句、JOIN或交叉应用/外部应用，但与简单视图不同，它不能作为任何DML语句(插入/更新/删除)的目标。这些也可以在T-SQL和SQLCLR中创建。

• T-SQL MultiStatement (TVF): these TVFs, as their name implies, can have multiple statements, similar to a Stored Procedure. Whatever results they are going to return are stored in a Table Variable and returned at the very end; meaning, nothing is returned until the function is done processing. The estimated number of rows that they will return, as reported to the Query Optimizer (which impacts the execution plan) depends on the version of SQL Server:

  T-SQL MultiStatement (TVF):顾名思义，这些TVFs可以有多个语句，类似于存储过程。无论返回的结果是什么，都存储在一个表变量中，并在最后返回;也就是说，在函数完成处理之前，不会返回任何内容。它们将返回的行估计数，如报告给查询优化器(影响执行计划)取决于SQL Server的版本:

  • Prior to SQL Server 2014: these always report 1 (yes, just 1) row.
  • 在SQL Server 2014之前:这些总是报告1(是的，只有1)行。
  • SQL Server 2014 and 2016: these always report 100 rows.
  • SQL Server 2014和2016:它们总是报告100行。
  • Starting in SQL Server 2017: default is to report 100 rows, BUT under some conditions the row count will be fairly accurate (based on current statistics) thanks to the new Interleaved Execution feature.
  • 从SQL Server 2017开始:默认是报告100行，但是在某些情况下，由于新的交错执行特性，行计数将相当准确(基于当前统计数据)。

• T-SQL Inline (iTVF): these TVFs can only ever be a single statement, and that statement is a full query, just like a View. And in fact, Inline TVFs are essentially a View that accepts input parameters for use in the query. They also do not cache their own query plan as their definition is placed into the query in which they are used (unlike the other objects described here), hence they can be optimized much better than the other types of TVFs ( ???? ). These TVFs perform quite well and are preferred if the logic can be handled in a single query.

  T-SQL Inline (iTVF):这些TVFs只能是一个语句，而该语句是一个完整的查询，就像一个视图。事实上，内联TVFs本质上是一个视图，它接受输入参数以便在查询中使用。他们自己也不缓存查询计划的定义是放入查询中使用(unlike here),描述的其他对象可以优化因此他们比其他类型的tvf(????).如果逻辑可以在单个查询中处理，则这些TVFs执行得很好，并且是首选。

• SQLCLR (TVF): these TVFs are similar to T-SQL MultiStatement TVFs in that they build up the entire result set in memory (even if it is swap / page file) before releasing all of it at the very end. The estimated number of rows that they will return, as reported to the Query Optimizer (which impacts the execution plan) is always 1000 rows. Given that a fixed row count is far from ideal, please support my request to allow for specifying the row count: Allow TVFs (T-SQL and SQLCLR) to provide user-defined row estimates to query optimizer

  SQLCLR (TVF):这些TVFs与T-SQL MultiStatement TVFs类似，它们在内存中构建整个结果集(即使是交换/页文件)，然后在最后释放所有结果集。它们将返回的估计行数(如报告给查询优化器(影响执行计划)的行数总是1000行。既然固定的行数并不理想，请支持我的请求，允许指定行数:允许TVFs (T-SQL和SQLCLR)向查询优化器提供用户定义的行估计

• SQLCLR Streaming (sTVF): these TVFs allow for complex C# / VB.NET code just like regular SQLCLR TVFs, but are special in that they return each row to the calling query as they are generated ( ???? ). This model allows the calling query to start processing the results as soon as the first one is sent so the query doesn't need to wait for the entire process of the function to complete before it sees any results. And it requires less memory since the results aren't being stored in memory until the process completes. The estimated number of rows that they will return, as reported to the Query Optimizer (which impacts the execution plan) is always 1000 rows. Given that a fixed row count is far from ideal, please support my request to allow for specifying the row count: Allow TVFs (T-SQL and SQLCLR) to provide user-defined row estimates to query optimizer

  SQLCLR流(sTVF):这些TVFs支持复杂的c# / VB。NET代码就像普通SQLCLR TVFs,但特别在他们返回的每一行调用查询生成(????).该模型允许调用查询在第一次发送时就开始处理结果，因此查询不需要等待函数的整个过程完成，然后才能看到任何结果。而且它需要更少的内存，因为直到进程完成，结果才会被存储在内存中。它们将返回的估计行数(如报告给查询优化器(影响执行计划)的行数总是1000行。既然固定的行数并不理想，请支持我的请求，允许指定行数:允许TVFs (T-SQL和SQLCLR)向查询优化器提供用户定义的行估计

Aggregate Functions

User-Defined Aggregates (UDA) are aggregates similar to SUM(), COUNT(), MIN(), MAX(), etc. and typically require a GROUP BY clause. These can only be created in SQLCLR, and that ability was introduced in SQL Server 2005. Also, starting in SQL Server 2008, UDAs were enhanced to allow for multiple input parameters ( ???? ). One particular deficiency is that there is no knowledge of row ordering within the group, so creating a running total, which would be relatively easy if ordering could be guaranteed, is not possible within a SAFE Assembly.

用户定义的聚合(UDA)是类似SUM()、COUNT()、MIN()、MAX()等的聚合，通常需要一个GROUP BY子句。这些只能在SQLCLR中创建，这种能力是在SQL Server 2005中引入的。Also,从SQL Server 2008,uda增强允许多个输入参数(????).一个特别的缺陷是，在组中不存在行排序知识，因此在一个安全的程序集中不可能创建一个运行的总数(如果可以保证排序的话，这将相对容易)。

---

Please also see:

也请见:

• CREATE FUNCTION (MSDN documentation)
• 创建函数(MSDN文档)
• CREATE AGGREGATE (MSDN documentation)
• 创建聚合(MSDN文档)
• CLR Table-Valued Function Example with Full Streaming (STVF / TVF) (article I wrote)
• 全流的CLR表值函数示例(STVF / TVF)

#2

A scalar function returns a single value. It might not even be related to tables in your database.

标量函数返回一个值。它甚至可能与数据库中的表无关。

A tabled-valued function returns your specified columns for rows in your table meeting your selection criteria.

表值函数将返回表中指定的列，以满足您的选择标准。

An aggregate-valued function returns a calculation across the rows of a table -- for example summing values.

一个聚集值函数返回一个表的行数，例如求和值。

#3

Scalar function

标量函数

Returns a single value. It is just like writing functions in other programming languages using T-SQL syntax.

返回一个值。这就像使用T-SQL语法用其他编程语言编写函数一样。

Table Valued function

表值函数

Is a little different compared to the above. Returns a table value. Inside the body of this function you write a query that will return the exact table. For example:

和上面的有点不同。返回一个表的价值。在该函数的主体内部，您将编写一个查询，该查询将返回确切的表。例如:

CREATE FUNCTION <function name>(parameter datatype)

RETURN table

AS

RETURN

(

-- *write your query here* ---

)

Note that there is no BEGIN & END statements here.

注意这里没有开始和结束语句。

Aggregate Functions

聚合函数

Includes built in functions that is used alongside GROUP clause. For example: SUM(),MAX(),MIN(),AVG(),COUNT() are aggregate functions.

包含与GROUP子句一起使用的内置函数。例如:SUM()、MAX()、MIN()、AVG()、COUNT()是聚合函数。