reuse sql with view or function

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 repeatin开发者_如何学Gog 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.

You cannot pass variables into a view, so your only option it seems is to use a function. There are two options for this:

• a SCALAR function
• a TABLE-VALUED function (inline or multi-statement)

If you were returning records, then you could use a WHERE clause from outside a not-too-complex VIEW which can get in-lined into the query within the view, but since all you are returning is a single column integer value, then a view won't work.

An inline TVF can be expanded by the query optimizer to work together with the outer (calling) query, so it can be faster in most cases when compared to a SCALAR function.

However, the usages are different - a SCALAR function returns a single value immediately

select dbo.scalarme(col1, col2), other from ..

whereas an inline-TVF requires you to either subquery it or CROSS APPLY against another table

select (select value from dbo.tvf(col1, col2)), other from ..

-- or

select f.value, t.other
from tbl t
CROSS apply dbo.tvf(col1, col2) f   -- or outer apply

I'm going to give you a half-answer because I cannot be sure about what is better in terms of performance, I'm sorry. But then other people have surely got good pieces of advice on that score, I'm certain.

I will stick to your 'common practice' part of question.

So, a scalar function wood seem to me a natural solution in this case. Why, you only want a value, an integer value to be returned - this is what scalar functions are for, isn't it?

But then, if I could see a probability that later I would need more than one value, I might then consider switching to a TVF. Then again, what if you have already implemented your scalar function and used it in many places of your application and now you need a row, a column or a table of values to be returned using basically the same logic?

In my view (no pun intended), a view could become something like the greatest common divisor for both scalar and table-valued functions. The functions would only need to apply the parameters.

Now you have said that you are only planning to choose which option to use. Yet, considering the above, I still think that views can be a good choice and prove useful when scaling your application, and you could actually use both views and functions (if only that didn't upset the performance too badly) just as I have described.

One advantage a TVF has has over a view is that you can force whoever calls it to target a specific index.