Solved

SQL: View vs. Function

Posted on 2014-09-19
4
370 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 25

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

929 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

12 Experts available now in Live!

Get 1:1 Help Now