SQL Server中标量，表值和聚合函数之间的区别？ [英] Difference between scalar, table-valued, and aggregate functions in SQL server?

问题描述

SQL服务器中标量值，表值和聚合函数之间有什么区别？并且从一个查询中调用它们是否需要另一种方法，或者我们以相同的方式调用它们？

标量函数

标量函数（有时称为用户定义函数/ UDF）返回单个值作为返回值，而不是结果集，可用于大多数地方在查询或 SET 语句中，除 FROM 子句（也许还有其他地方）外。另外，标量函数可以通过 EXEC 调用，就像存储过程一样，尽管没有太多场合可以使用这个能力（关于这个能力的更多细节，请看我对DBA.StackExchange上的以下问题的回答：为什么标量值函数需要执行权限而不是选择？）。这些可以在T-SQL和SQLCLR中创建。

• T-SQL（UDF）：这些标量函数通常是一个性能问题，因为它们通常针对返回（或扫描）的每一行运行，而和总是禁止并行执行计划。 SQLCLR （UDF）：这些标量函数通常对每个返回或扫描的每行都运行，但与T-SQL UDF相比，有两个重要优势：
  
  
  
  
  • 从SQL Server 2012开始，如果UDF不执行任何数据访问，并且标记为，则返回值可以常量折叠到执行计划中。 IsDeterministic = true 。在这种情况下，函数不会每行都运行。
    
  • SQLCLR标量函数可以在并行计划中运行（

    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?

    解决方案

    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.

    • 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.

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

      • 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.
      • SQLCLR scalar functions can work in parallel plans (

        这篇关于SQL Server中标量，表值和聚合函数之间的区别？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！