reuse sql with view or function

Question

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

Solution 1

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.

Solution 2

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

Comments

[Martin Smith]

A view can return a single column single row result. Admittedly not a usual case but still probably more efficient than scalar UDFs. Edit - Ah right you were questioning the parameterisation aspect. I assumed that would be via a predicate on the view.

[amateur]

I can join to the view and restrict results based on a where clause

[Andomar]

You'd normally "pass" in-parameters to a view with a where clause

[RichardTheKiwi]

@Andomar, @Niall - How do you pass parameters into this view that returns only one int? <br> create view v_spt_values as select COUNT(*) C from master..spt_values <br> select * from v_spt_values where TYPE = 'P' <br> Output - Msg 207, Level 16, State 1, Line 1 Invalid column name 'TYPE'.

[Andomar]

@cyberwiki: Something like select * from vw_Products where id = 1; the view is built with the assumption that the caller will use it for a specific product.

[RichardTheKiwi]

@andomar - Can you make that answer relevant to the view in the comment above yours? I don't see how that would work.

[ErikE]

The view in the comment above wouldn't need parameters. This is silly.

[RichardTheKiwi]

@Emtucifor - That's the type of view posed by the OP (single scalar value), am I missing something? I assumed in line with Martin and Niall's comments that if the view had columns X,Y,Z you can perform a predicate on X and Y to get it inlined. But not if the view only returned Z as shown in my prior comment.

[onedaywhen]

I think your interpretation "returns an integer" to mean "return a scalar value" may be too literal. If you wanted to be able to search your VIEW by type you can simply amend it e.g. CREATE VIEW v_spt_values (C, TYPE) AS SELECT COUNT(*), TYPE FROM master..spt_values GROUP BY TYPE;

[RichardTheKiwi]

@onedaywhen ok got it. i took it literally