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
  • No reason not to use a View for join(s). Go with what is the most simple - View. – paparazzo Mar 14 '17 at 17:54
  • Things to try avoid are multi statement UDFs and nesting views inside views. Both can cause quite bad performance. – James Z Mar 14 '17 at 18:24
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.

0

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.

  • 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 Mar 14 '17 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 Mar 14 '17 at 18:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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