首页 文章

功能与存储过程

提问于
浏览 次
84

假设我必须实现一段必须返回表的T-SQL代码 . 我可以实现一个表值函数或一个返回一组行的存储过程 . 我该怎么用？

简而言之，我想知道的是：

Which are the main differences between functions and stored procedures? 使用其中一个时需要考虑哪些因素？

sql sql-server database function stored-procedures

11 回答

  • 42

    如果您可能希望将这段代码的结果与其他表组合在一起，那么显然表值函数将允许您在单个SELECT语句中组合结果 .

    通常，有一个层次结构（View <TV Function <Stored Proc） . 您可以在每个中执行更多操作，但是随着功能的增加，组合输出以及优化器真正参与的能力会降低 .

    因此，请使用最低限度允许您表达所需结果的任何一种 .

    回复于
  • 48

    函数必须是确定性的，不能用于更改数据库，而存储过程允许您进行插入和更新等 .

    您应该限制对函数的使用，因为它们会给大型复杂查询带来巨大的可伸缩性问题 . 它们成为查询优化器的“黑盒子”，你会发现使用函数和简单地将代码插入查询之间的性能差异很大 .

    但在特定情况下，它们对于表值返回肯定有用 .

    如果需要解析以逗号分隔的列表，以模拟将数组传递给过程，则函数可以将列表转换为表格 . 这是Sql Server 2005的常见做法，因为我们还不能将表传递给存储过程（我们可以使用2008） .

    回复于
  • 6

    From the docs：

    如果存储过程符合以下条件，则可以将其重写为表值函数：逻辑可以在单个SELECT语句中表达，但只是因为需要而是存储过程而不是视图参数 . 除表变量外，存储过程不执行更新操作 . 不需要动态EXECUTE语句 . 存储过程返回一个结果集 . 存储过程的主要目的是构建要加载到临时表中的中间结果，然后在SELECT语句中查询该临时表 .

    回复于
  • 49

    我将在存储过程和函数之间写下一些有趣的差异 .

    • 我们可以在select查询中使用函数，但我们不能在select查询中使用存储过程 .

    • 我们不能在函数中使用非确定性函数，但我们可以在存储过程中使用非确定性函数 . 现在问题出现了，什么是非确定性函数..答案是： -

    非确定性函数是在不同时间为相同输入值返回不同输出的函数，如getdate（） . 它始终在运行时返回不同的值 .

    例外：-

    sql 2000之前的早期版本的sql server不允许在用户定义的函数中使用getdate（）函数，但是版本2005及更高版本允许我们在用户定义的函数中使用getdate（）函数 . Newid（）是非确定性函数的另一个例子，但不能在用户定义的函数中使用，但我们可以在存储过程中使用它 .

    • 我们可以在存储过程中使用DML（插入，更新，删除）语句，但我们不能在物理表或永久表的函数中使用DML语句 . 如果我们想在函数中进行DML操作，我们可以在不在永久表上的表变量上进行 .

    • 我们不能在函数中使用错误处理，但我们可以在存储过程中进行错误处理 .

    回复于
  • 5

    • 过程可以返回零或n值，而函数可以返回一个必需的值 .

    • 程序可以有输入/输出参数，而函数只能有输入参数 .

    • Procedure允许select和DML语句，而function只允许select语句 .

    • 可以从过程调用函数，但不能从函数调用过程 .

    • 异常可以通过过程中的try-catch块来处理，而try-catch块不能在函数中使用 .

    • 我们可以在程序中进行交易管理，但是我们不能进入功能 .

    • 过程不能在select语句中使用，而函数可以嵌入在select语句中 .

    • UDF（用户定义函数）可以在 WHERE / HAVING / SELECT 部分的任何位置的SQL语句中使用，而存储过程不能 .

    • 返回表的UDF可以视为另一个行集 . 这可以在 JOIN 中与其他表一起使用 .

    • 内联UDF可以作为带参数的视图，可以在 JOIN 和其他行集操作中使用 .

    回复于
  • 4

    例如，如果您有一个函数，则可以将它用作SQL语句的一部分

    SELECT function_name(field1) FROM table

    它对存储过程不起作用 .

    回复于
  • 8

    一世使用长时间运行的逻辑运行一些测试，在表值函数和存储过程中运行相同的代码位（一个长的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

    我在几个函数中使用过这种技术，有时会使用这种类型的“可选”参数列表 .

    回复于
  • 10

    我个人使用表值函数时，我返回的是一个没有影响的单个表 . 基本上我将它们视为参数化视图 .

    如果我需要返回多个记录集或者如果表中有值更新，我使用存储过程 .

    我的2美分

    回复于
  • 4

    如上所述，函数更具可读性/可组合性/自我记录，但一般性能较差，如果你在连接中被带走，可能会严重降低性能 .

    SELECT *
FROM dbo.tvfVeryLargeResultset1(@myVar1) tvf1
INNER JOIN dbo.tvfVeryLargeResultset1(@myVar2) tvf2
    ON (tvf1.JoinId = tvf2.JoinId)

    通常，您只需接受tvf可以消除的代码冗余（以不可接受的性能成本） .

    One other point I haven't yet seen mentioned is that you can't use database state-changing temp tables inside of a multi-statement tvf. 临时表的功能最等同的机制是内存表变量中的非状态更改，对于大型数据集，临时表可能比表变量更高性能 . （其他替代方案包括动态表和公用表值表达式，但在某种程度上的复杂性，这些不再是IMO的好选择 . ）

    回复于
  • 1

    我会对两者进行性能测试 . sp方法或派生表可能比函数快得多，如果是这样，则应使用该方法 . 一般来说，我避免使用函数，因为它们可能是性能损失 .

    回复于
  • 1

    这取决于:)如果你想在另一个程序中使用表值结果，你最好使用TableValued函数 . 如果结果是针对客户端的，那么存储过程通常是更好的方法 .

    回复于

相关问题