在视图或函数中重用sql [英] reuse sql with view or function

问题描述

我有一个sql查询，我将在多个存储过程中重复使用。查询对多个表起作用，并根据传递给它的2个变量返回一个整数值。

与其在不同的存储过程中重复查询，我想分享它并拥有2个选项：

1. 创建一个视图，我可以根据这些变量加入并从中获取整数值。


2. 使用传递给它的条件再次创建一个函数并返回整数变量

我倾向于选项1，但想知道哪些更好，哪些常见做法。这将是更好的性能明智等等（加入视图或调用函数）编辑：RDBMS是SQL Server

解决方案

如果你总是使用相同的参数化谓词来过滤结果，那么我会去参数化的内联表值函数。从理论上讲，这与View相同，它们都被优化器扩展出来，实际上它可以避免谓词推动问题。这种情况的例子可以在

我认为View的一个优点是它可以让你在没有过滤器或不同过滤器的情况下进行选择，因此功能更加通用。

内联TVF也可以用来取代标量UDF以提高效率在这个例子中。

I have a sql query that I will be reusing in multiple stored procedures. The query works against multiple tables and returns an integer value based on 2 variables passed to it.

Rather than repeating the query in different stored procedures I want to share it and have 2 options:

1. create a view to which I can join to based on the variables and get the integer value from it.
2. create a function again with criteria passed to it and return integer variable

I am leaning towards option 1 but would like opinions on which is better and common practice. Which would be better performance wise etc. (joining to a view or calling function)

EDIT: The RDBMS is SQL Server

解决方案

If you will always be using the same parametrised predicate to filter the results then I'd go for a parametrised inline table valued function. In theory this is treated the same as a View in that they both get expanded out by the optimiser in practice it can avoid predicate pushing issues. An example of such a case can be seen in the second part of this article.

As Andomar points out in the comments most of the time the query optimiser does do a good job of pushing down the predicate to where it is needed but I'm not aware of any circumstance in which using the inline TVF will perform worse so this seems a rational default choice between the two (very similar) constructs.

The one advantage I can see for the View would be that it would allow you to select without a filter or with different filters so is more versatile.

Inline TVFs can also be used to replace scalar UDFs for efficiency gains as in this example.

这篇关于在视图或函数中重用sql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！