提问人:Auron 提问时间:10/7/2008 最后编辑:bluishAuron 更新时间:12/25/2019 访问量:43819
函数与存储过程
Functions vs Stored Procedures
问:
假设我必须实现一段 T-SQL 代码,该代码必须返回一个表作为结果。我可以实现一个表值函数,或者一个返回一组行的存储过程。我应该使用什么?
简而言之,我想知道的是:
函数和存储过程之间的主要区别是什么?使用其中之一时,我必须考虑哪些因素?
答:
如果您可能希望将这段代码的结果与其他表组合在一起,那么显然表值函数将允许您在单个 SELECT 语句中编写结果。
通常,有一个层次结构(查看<电视功能<存储过程)。您可以在每个选项中执行更多操作,但是随着功能的增加,编写输出以及优化器真正参与的能力会降低。
因此,使用最低限度的任何一个都可以让您表达所需的结果。
我个人使用表值函数,当我返回的只是一个没有影响的表时。基本上,我将它们视为参数化视图。
如果我需要返回多个记录集,或者如果表中将更新值,则使用存储过程。
我的 2 美分
例如,如果您有一个函数,则可以将其用作 SQL 语句的一部分
SELECT function_name(field1) FROM table
对于存储过程,它不能以这种方式工作。
评论
函数必须是确定性的,不能用于对数据库进行更改,而存储过程允许您执行插入和更新等操作。
您应该限制函数的使用,因为它们会给大型复杂查询带来巨大的可伸缩性问题。它们成为查询优化器的“黑匣子”,您会看到使用函数和简单地将代码插入查询之间的性能存在巨大差异。
但是,在非常特殊的情况下,它们对于表值回报绝对有用。
如果需要解析逗号分隔的列表,以模拟将数组传递给过程,函数可以将列表转换为表。这是 Sql Server 2005 的常见做法,因为我们还不能将表传递给存储过程(我们可以在 2008 中)。
评论
这取决于:)如果要在另一个过程中使用表值结果,最好使用 TableValued 函数。如果结果是针对客户端的,则存储的过程通常是更好的方法。
从文档中:
如果存储过程满足以下条件,则该存储过程非常适合重写为表值函数:
该逻辑可在单个 SELECT 语句中表示,但它是一个存储过程,而不是视图,只是因为需要参数。
存储过程不执行更新操作,但对表变量执行更新操作除外。
不需要动态 EXECUTE 语句。
存储过程返回一个结果集。
存储过程的主要用途是生成要加载到临时表中的中间结果,然后在 SELECT 语句中查询该表。
我会对两者都进行性能测试。sp 方法或派生表可能比函数快得多,如果是这样,则应使用该方法。一般来说,我避免使用函数,因为它们可能是性能消耗者。
如上所述,函数的可读性/可组合性/自我记录性更强,但总体性能较差,如果您在诸如
SELECT *
FROM dbo.tvfVeryLargeResultset1(@myVar1) tvf1
INNER JOIN dbo.tvfVeryLargeResultset1(@myVar2) tvf2
ON (tvf1.JoinId = tvf2.JoinId)
通常,您只需要接受 tvf 可以消除的冗余代码(以不可接受的性能成本)。
我还没有看到提到的另一点是,你不能在多语句 tvf 中使用数据库状态更改临时表。与临时表功能最等效的机制是非状态更改,内存表变量,对于大型数据集,临时表可能比表变量的性能更高。(其他替代方案包括动态表和通用表值表达式,但在某种程度上复杂程度上,这些不再是一个好的选择。
我运行了一些测试,其中有很长的逻辑位,在表值函数和存储过程中运行相同的代码位(一个长的 SELECT 语句),以及一个直接的 EXEC/SELECT,并且每个测试的执行方式相同。
在我看来,始终使用表值函数而不是存储过程来返回结果集,因为它使逻辑在随后加入它们的查询中更容易和可读,并使您能够重用相同的逻辑。为了避免对性能造成太大影响,我经常使用“可选”参数(即您可以将 NULL 传递给它们)来使函数能够更快地返回结果集,例如:
CREATE FUNCTION dbo.getSitePermissions(@RegionID int, @optPersonID int, optSiteID int)
AS
RETURN
SELECT DISTINCT SiteID, PersonID
FROM dbo.SiteViewPermissions
WHERE (@optPersonID IS NULL OR @optPersonID = PersonID)
AND (@optSiteID IS NULL OR @optSiteID = SiteID)
AND @RegionID = RegionID
这样,您就可以在许多不同的情况下使用此功能,并且不会对性能造成巨大影响。我相信这比事后过滤更有效:
SELECT * FROM dbo.getSitePermissions(@RegionID) WHERE SiteID = 1
我已经在几个函数中使用了这种技术,有时有一长串这种类型的“可选”参数。
过程可以返回零或 n 个值,而函数可以返回 一个必填值。
过程可以有输入/输出参数,而函数只能有输入参数。
Procedure 允许在其中使用 select 和 DML 语句,而函数只允许在其中使用 select 语句。
函数可以从过程中调用,而过程不能 调用自 函数。
异常可以通过过程中的 try-catch 块处理,而 try-catch 块不能在函数中使用。
我们可以在程序中进行事务管理,而不能在功能中进行。
过程不能在 select 语句中使用,而函数可以嵌入到 select 语句中。
UDF(用户定义函数)可以在 // 部分的任意位置的 SQL 语句中使用,而存储过程则不能。
WHERE
HAVING
SELECT
可以将返回表的 UDF 视为另一个行集。这可以与其他表一起使用。
JOIN
内联 UDF 可以作为采用参数的视图,并且可以在 s 和其他行集操作中使用。
JOIN
我将写一些存储过程和函数之间的有趣差异。
- 我们可以在选择查询中使用函数,但不能使用存储 选择查询中的过程。
我们不能在函数中使用非确定性函数,但我们可以 在存储过程中使用非确定性函数。 现在问题来了,什么是非确定性函数。回答是:-
非确定性函数是在不同时间为相同的输入值返回不同输出的函数,例如 getdate()。每当它运行时,它总是返回不同的值。
例外:-
SQL 2000 之前的早期版本不允许在用户定义的函数中使用 getdate() 函数,但 2005 及更高版本允许我们在用户定义的函数中使用 getdate() 函数。
Newid() 是非确定性函数的另一个示例,但不能在用户定义的函数中使用,但我们可以在存储过程中使用它。
我们可以在存储的 DML(插入、更新、删除)语句中使用 过程,但我们不能在物理函数中使用 DML 语句 表或永久表。如果我们想在 函数,我们可以在表变量上完成,而不是在永久表上。
我们不能在函数中使用错误处理,但我们可以做错误 在存储过程中处理。
评论
存储过程是预编译的查询,执行速度更快,并且从 sql 注入中保存。它们可以返回 0 或 N 值。我们可以在存储过程中执行 DML 操作。我们可以在过程中使用函数,也可以在选择查询中使用函数。 函数用于返回函数中不可能的任何值和 DML 操作。 函数有两种类型:Scalar 和 tabled-valued。标量函数返回单个值,即用于返回表行的表值函数。
评论