视图还是用户定义的函数？
我有一个场景，我将两个表合并为一个（使用 UNION），并且还连接来自其他一些表的数据。
我可以使用 VIEW 或 UDF/用户定义函数来完成此操作。
考虑到到目前为止我的数据库中还没有任何视图，但有相当多的 UDF 用于全文搜索等，我很想在这种情况下使用 UDF 来保持它的“干净”。
然而，这完全是主观的，我想知道在这种情况下是否有更好的客观理由选择 VIEW 或 UDF。
我确实比较了两者的查询计划，它们是完全相同的，所以我不相信使用任何一个都会有性能损失或优势。
是否还有其他理由选择其中之一，或者这并不重要？
I have a scenario where I combine two tables into one (using UNION) and also JOIN data from some other tables.
I can do this either with a VIEW or a UDF/User Defined Function.
Considering that I have no view so far at all in my database, but quite a few UDF's for fulltext search and the like, I'm tempted to use a UDF for this scenario as well to keep it "clean".
However, this is entirely subjective and I wondered if there is a better objective reason to go for either a VIEW or a UDF in this scenario.
I did compare query plans for both and they are the exact same so I don't believe there is a performance penalty or advantage for using either one.
Is there other reason to choose one over the other or does it not matter?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（2）
我总是按照复杂程度的顺序使用功能。就性能概况和安全管理而言，视图相对简单。我可能会首先使用它。
我假设您正在谈论内联表值 UDF，它几乎具有相同的性能特征。 UDF 的安全性略有不同，您不能使用触发器来对视图进行“插入”。 UDF 的一个好处是您可以强制提供参数，从而确保使用模式符合预期，而可以在没有任何条件的情况下查询视图（可能是无意的）。
如果您最终确实想要使用 UDF 进行参数化，则可以将其分层在视图之上（这样就不会重复代码），并且您应该会发现性能不会受到显着影响，因为优化器可以公平地结合视图和内联 TVF成功地。
I would always use features in order of sophistication. A view is relatively straightforward in terms of performance profile and security management. I would probably use it first.
I'm assuming you are talking about an inline table-valued UDF, which will pretty much have identical performance characteristics. Security on a UDF is a little different and you cannot have instead of triggers to be able to do "inserts" on the view. A benefit of the UDF would be that you can force parameters to be supplied, thereby ensuring usage patterns are as expected, whereas a view can be queried without any criteria (perhaps accidentally).
If you did end up wanting to have a UDF for parameterization, you could layer it on top of the view (so no code duplication) and you should find that the performance is not affected significantly, because the optimizer can combine views and inline TVF fairly successfully.
我看到使用此场景作为视图的一个优点是对它们进行索引并将它们用作“索引视图”，与传统视图不同，它会创建物理文件，因此在其中有大量行的情况下查询速度更快。使用它的真正效果是绕过所有行的联接和并集，而只为新行构建它们。
正如我们的朋友 Cade 所建议的，您可以在 UDF 中使用视图来保持其整洁，这并没有太大不同。
希望这有帮助！
One advantage i see by using this scenario as view is to index them and use them as "indexed views" where unlike a traditional view there is physical file that is created and therefore querying is faster where there are considerable amount of rows in it. The very effect of using this is to bypass the joins and unions for all the rows instead only build them for new rows.
As our friend Cade suggested, you cud use a view inside a UDF to keep it clean and it isn't a lot different.
Hope this helps !