1

Is it just down to preference for whether to use a View or a stored procedure (or TVF) to simply return data joined from multiple base tables?

Is there any best practice regarding this? Should a stored procedure be used just for manipulating data? And is it best to use a view for the purpose of returning data from multiple tables?

Are there also likely to be any performance issues?

Query:

SELECT
    t.TagID, t.TagName, 
    t.IsActive AS 'IsTagActive',
    tk.TaskID, tk.TaskName,
    tk.IsActive AS 'IsTaskActive',
    g.GroupID, g.GroupName
FROM 
    Model.[Tag] t
INNER JOIN
    Model.[TagTaskMapping] ttm ON ttm.TagID = t.TagID
INNER JOIN  
    Model.[Task] tk ON tk.TaskID = ttm.TaskID
INNER JOIN
    Model.[Group] g ON g.GroupID = t.GroupID
Improve this question
2
  • 1
    No reason not to use a View for join(s). Go with what is the most simple - View.
    – paparazzo
    Commented Mar 14, 2017 at 17:54
  • 1
    Things to try avoid are multi statement UDFs and nesting views inside views. Both can cause quite bad performance.
    – James Z
    Commented Mar 14, 2017 at 18:24

2 Answers 2

Reset to default
1

A view is a good approach for when you need to flatten or consistently address how various base tables are organized, essentially de-normalizing the data for the consuming application. Your example includes tasks, tags and group information as a cohesive whole.

Alternatively, views can be used to restrict data consistently. For example if the definition of an "open" purchase order was complex then a view could streamline that.

Stored Procedures and Table Valued Functions are quite a bit different. They can accept arguments and include branching or more complex logic while still offering a simple interface for the client applications. Stored procedures can also modify data (logging tables) if needed.

I have also seen stored procedures used as a method to avoid parameter sniffing or getting better execution plans. A master stored procedure which determines whether to call sub-procedure 1 for a small amount of data or sub procedure 2 for a large amount of data.

Table Valued Functions are more similar to Stored Procedures than views and accept arguments, but can be used in joins. Their performance is generally terrible and should be used cautiously.

Improve this answer
2
  • Inline Table Valued Functions perform much better than multi-statement table valued functions. When is a SQL function not a function? "If it’s not inline, it’s rubbish." - Rob Farley
    – SqlZim
    Commented Mar 14, 2017 at 18:04
  • If you can simplify your TVF query to the point where it qualifies as inline, you didn't really need a TVF to begin with. But you are correct that they perform better than multi-statement table valued functions. But the point of TVF is to allow for more complex logic than can be achieved in a single statement but without requiring the use of a stored procedure.
    – Jonathan Fite
    Commented Mar 14, 2017 at 18:12
0

This seems like a strange question to me, but here are some opinionated brief one liner guidelines for each.

  • stored procedure: If you want use parameters and are not joining to the results.

  • in-line table-valued function: If you want use parameters and might join the results.

  • views: If you want to simplify querying a set of tables, and promise not to make views that include other views.

What you have posted looks like it would be a view to me. Unless you want to pass in parameters, then I would say it would probably be a stored procedure.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.