| Author |
Topic |
|
kamii47
Constraint Violating Yak Guru
353 Posts |
 Posted - 2009-06-18 : 04:12:35
|
| Which is best to return result set.Stored procedure or Table value function.Please explain a littleKamran ShahidSr. Software EngineerAssurety Consulting inc.(MCSD.Net,MCPD.net) |
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-18 : 04:18:32
|
As in most cases, "It depends".It depends on the nature of data, which consumers of data there are and so on...
E 12°55'05.63"N 56°04'39.26" |
 |
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2009-06-18 : 04:23:22
|
| I have read http://www.sql-server-performance.com/faq/stored_procedure_function_p1.aspxin one of a line it is written that "user-defined functions are also pre-optimized and compiled similarly to stored procedures (unlike what you have been told). Even so, they have more overhead than corresponding stored procedures."I want's to know what overhead it mean't if Table-Value function are also optimized and precompiled.Kamran ShahidSr. Software EngineerAssurety Consulting inc.(MCSD.Net,MCPD.net) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-18 : 04:27:12
|
It depends on what you are going to use it for!If you like to use the resultset from a function, you can use CROSS APPLY to incorporate the resultset into your query.A resultset from a stored procedure only be used to insert the resultset into a table (unless you make use of openrowset and linked server of course).So they have different arenas of excellence. They both produce a resultset (a function can also produce a scalar value), but the use of the data totally differs them apart.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2009-06-18 : 04:35:59
|
| Suppose I am just returning the result set to my DAL where i populate it in a DataTable?Then what should be the choice?Kamran ShahidSr. Software EngineerAssurety Consulting inc.(MCSD.Net,MCPD.net) |
|
|
|
SwePeso
Patron Saint of Lost Yaks
30421 Posts |
Posted - 2009-06-18 : 04:53:25
|
Stored procedure.
E 12°55'05.63"N 56°04'39.26" |
|
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2009-06-18 : 07:03:02
|
| Thanks Peso [I always follow yours post and learns a lot from them]Can you please give me the detail reason if you can?I have found some detail why Sql server function [here it is for scalar] have more overhead as[url]http://stackoverflow.com/questions/800017/why-do-sql-server-scalar-valued-functions-get-slower[/url]Kamran ShahidSr. Software EngineerAssurety Consulting inc.(MCSD.Net,MCPD.net) |
|
|
|
saran_d28
Starting Member
36 Posts |
Posted - 2009-06-19 : 05:10:20
|
If we use tables and views then stored procedure is give better performance then inline function.If we use indexed view then Inline function give better performance then stored procedure.--Saravanan |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2009-06-21 : 21:01:10
|
To quote someone famous... "One experiment is worth a thousand expert opinions". Why don't you set up a high scale test and find out for yourself?  --Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row"For better, quicker answers, click on the following... [url]http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url] |
|
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2009-06-30 : 01:24:37
|
| One more thing is that what I have seen is that in my existing code there is code for creating query and assigning parameters to them.I mean it prepare the query likeselect * from myfunct('myparameter1', 2, False)And then this query is executed for getting the resultset.Any comment about itKamran ShahidSr. Software EngineerAssurety Consulting inc.(MCSD.Net,MCPD.net) |
|
|
|
Jeff Moden
Aged Yak Warrior
652 Posts |
Posted - 2009-06-30 : 07:54:48
|
Yes... it's no different than a stored procedure. The parameter gets assigned to a variable in the definition of the function and you use the variable as criteria. The only thing you can't do in a function is dynamic SQL so this won't work if your parameter has anything to do with the FROM clause.I also recommend that you take a look at CREATE FUNCTION in Books Online (the "help" system which comes free with SQL Server)--Jeff Moden
"Your lack of planning DOES constitute an emergency on my part... SO PLAN BETTER! ""RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row" |
|
|
|
kamii47
Constraint Violating Yak Guru
353 Posts |
Posted - 2009-07-07 : 08:16:38
|
| Thanks Jeff, So my understanding after all this discussion is to use Stored procedure.I have Also posted my question on microsoft forum [url]http://social.msdn.microsoft.com/Forums/en-US/transactsql/thread/2a6b7bcf-8c8d-49fc-9949-ff79f30f1a0e/?prof=required [/url] where I were also advised to Use Stored proceduresKamran ShahidSr. Software EngineerAssurety Consulting inc.(MCSD.Net,MCPD.net) |
|
|
|
|
Stored procedure or Table value function