Solved

SQL: View vs. Function

Posted on 2014-09-19
4
367 Views
Last Modified: 2014-09-29
I have an existing stored procedure (300 lines of code) for a report that creates two temp tables.

tempTable A gets all accounts that match a certain criteria. This table is then treated as a reference table.

tempTable B gets all accounts that match the reference accounts in tempTable A.

Lastly, a select statement is called to get all records from tempTable B.

There is now a need to modify an existing stored procedure called ABC for a different report, and I really don't want to COPY/PASTE 300 lines of code from one stored procedure to another, so I was thinking of creating a table-valued function and COPY/PASTE those 300 lines into it, and then just call this function from the ABC stored procedure.

However, I was also suggested that I could create a view instead, and perhaps a view could be more efficient?

I need your advice on how to design this further for stored procedure ABC.
0
Comment
Question by:pzozulka
4 Comments
 
LVL 48

Expert Comment

by:PortletPaul
ID: 40333885
a view on temp tables may also fail (when one or more of the tables aren't there)
0
 
LVL 21

Expert Comment

by:Dale Burrell
ID: 40333934
An inline table valued function should perform just as well as a view, while allowing a greater level of control e.g. using parameters etc
0
 
LVL 25

Assisted Solution

by:jogos
jogos earned 250 total points
ID: 40334275
First reading: copying the 300 lines of code from first procedure to UDF and call that UDF from old and new procedure means that you probably don't need a second procedure.  But guess that is to simple.

Different options and very based on what the difference is between procedures: criteria for table A, criteria or data for table B and/or the statement for your report data.  

There is the maintainability
- selection must be reliable, changes in one report must/may not  inpact in other report
- performance : touch as little data as possible from start to end of report


When criteria differ for the tempTable A
Make an (inline table valued ) UDF with parameters that returns the  selected accounts.  

Can A and B get combined?

Using views in a more complex situation (views on views, adding rownumbers....)  have the problem that the filter you apply on the end is translated in the usage of the proper index for that resultset.   For example you report on year or month-base your view does not know that you finnaly wil only filter the current month. An udf with a year/month parameter or daterange there the sql engine has less possibility to miss the index for that criteria.
If report A is all customers for last month then you make a udf that has date-parameters.   If report B is to report all customers for category x products then your filtering will be on a whole other level.

Separating filtering it in UDF's can then get the efect you touch same data on 2 occasions what may impact your performance.
0
 
LVL 24

Accepted Solution

by:
Tomas Helgi Johannsson earned 250 total points
ID: 40335141
Hi!

Using UDF's (user defined functions ) in views, other UDF's or SP is very common when
1 ) the SQL of a view becomes to complex
2 ) parts of the sql can be reused in elsewhere  ( code reuse :)  )

The only thing that matters is that the sql is as fast and efficient  as possible. :)

Stored Procedures are great for data and sql where a join in a view becomes impossible or is way to slow due to complex data casting/manipulation before a join could occur. In such case  temporary tables  in a SP may become necessary to hold and process the data to achieve desired results.

Regards,
    Tomas Helgi
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.‚Äč
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now