在SQL中什么时候应该使用函数而不是存储过程,反之亦然?每一个的目的是什么?


当前回答

我知道这是一个非常老的问题,但是我在任何答案中都没有看到一个关键的方面:内联到查询计划中。

函数可以是…

标量: 创建函数…返回scalar_type AS BEGIN…结束 Multi-statement表值: 创建函数…返回@r表(…)开始…结束 内联表值: 创建函数…返回表作为return select…

第三种(内联表值)被查询优化器本质上视为(参数化)视图,这意味着从查询中引用函数类似于复制粘贴函数的SQL主体(而不是实际的复制粘贴),导致以下好处:

查询计划器可以优化内联函数的执行,就像它会优化任何其他子查询一样(例如,消除未使用的列,下推谓词,选择不同的JOIN策略等)。 组合几个内联函数并不需要在将第一个函数的结果提供给下一个函数之前实现它。

上述方法可能会带来潜在的显著性能节省,特别是在组合多个级别的功能时。


注意:看起来SQL Server 2019也将引入某种形式的标量函数内联。

其他回答

存储过程用作脚本。它们为您运行一系列命令,您可以安排它们在特定时间运行。通常运行多个DML语句,如INSERT, UPDATE, DELETE等,甚至是SELECT。

函数用作方法。你给它传递一些东西,它会返回一个结果。应该是小而快速的-在飞行中。通常在SELECT语句中使用。

要决定何时使用以下几点可能会有所帮助-

存储过程不能返回表变量,而函数可以这样做。 您可以使用存储过程来更改服务器环境参数,而使用函数则不能。

干杯

下面是一个总结差异的表格:

Stored Procedure Function
Returns Zero or more values A single value (which may be a scalar or a table)
Can use transaction? Yes No
Can output to parameters? Yes No
Can call each other? Can call a function Cannot call a stored procedure
Usable in SELECT, WHERE and HAVING statements? No Yes
Supports exception handling (via try/catch)? Yes No
STORE PROCEDURE FUNCTION (USER DEFINED FUNCTION)
Procedure can return 0, single or multiple values Function can return only single value
Procedure can have input, output parameters Function can have only input parameters
Procedure cannot be called from a function Functions can be called from procedure
Procedure allows select as well as DML statement in it Function allows only select statement in it
Exception can be handled by try-catch block in a procedure Try-catch block cannot be used in a function
We can go for transaction management in procedure We can not go for transaction management in function
Procedure cannot be utilized in a select statement Function can be embedded in a select statement
Procedure can affect the state of database means it can perform CRUD operation on database Function can not affect the state of database means it can not perform CRUD operation on database
Procedure can use temporary tables Function can not use temporary tables
Procedure can alter the server environment parameters Function can not alter the environment parameters
Procedure can use when we want instead is to group a possibly- complex set of SQL statements Function can use when we want to compute and return a value for use in other SQL statements

存储过程与用户自定义函数的区别:

Stored procedures cannot be used in Select statements. Stored procedures support Deferred Name Resolution. Stored procedures are generally used for performing business logic. Stored procedures can return any datatype. Stored procedures can accept greater numbers of input parameter than user defined functions. Stored procedures can have up to 21,000 input parameters. Stored procedures can execute Dynamic SQL. Stored procedures support error handling. Non-deterministic functions can be used in stored procedures.


User-defined functions can be used in Select statements. User-defined functions do not support Deferred Name Resolution. User-defined functions are generally used for computations. User-defined functions should return a value. User-defined functions cannot return Images. User-defined functions accept smaller numbers of input parameters than stored procedures. UDFs can have up to 1,023 input parameters. Temporary tables cannot be used in user-defined functions. User-defined functions cannot execute Dynamic SQL. User-defined functions do not support error handling. RAISEERROR OR @@ERROR are not allowed in UDFs. Non-deterministic functions cannot be used in UDFs. For example, GETDATE() cannot be used in UDFs.