MS Sql Server: Stored procedures vs. functions

In many instances you can accomplish the same task using either a stored procedure or a function. Both functions and stored procedures can be custom defined and part of any application. Functions, on the other hand, are designed to send their output to a query or T-SQL statement. For example, User Defined Functions (UDFs) can run an executable file from SQL SELECT or an action query, while Stored Procedures (SPROC) use EXECUTE or EXEC to run. Both are instantiated using CREATE FUNCTION. UDFs are instantiated using CREATE FUNCTION and SPROC instantiated by using CREATE PROCEDURE.

To decide between using one of the two, keep in mind the fundamental difference between them: stored procedures are designed to return its output to the application. A UDF returns table variables, while a SPROC can’t return a table variable although it can create a table. Another significant difference between them is that UDFs can’t change the server environment or your operating system environment, while a SPROC can. Operationally, when T-SQL encounters an error the function stops, while T-SQL will ignore an error in a SPROC and proceed to the next statement in your code (provided you’ve included error handling support). You’ll also find that although a SPROC can be used in an XML FOR clause, a UDF cannot be.

If you have an operation such as a query with a FROM clause that requires a rowset be drawn from a table or set of tables, then a function will be your appropriate choice. However, when you want to use that same rowset in your application the better choice would be a stored procedure.

There’s quite a bit of debate about the performance benefits of UDFs vs. SPROCs. You might be tempted to believe that stored procedures add more overhead to your server than a UDF. Depending upon how your write your code and the type of data you’re processing, this might not be the case. It’s always a good idea to text your data in important or time-consuming operations by trying both types of methods on them.

Reference: http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1063700,00.html

Regards,
Vijay Modi

Advertisements

About Vijay Modi

Having 12+ years of experience in web application development. Expertise in various domains like E-Commerce, E-Learning, Insurance. I have expertise in web application development, window application development, Performance improvement, bug fixing etc. I have worked on various Microsoft technologies including ASP.Net, MVC, WebApi, WCF, Entity Framework, WPF, Window Application Development, AWS, Azure, AngularJS 2.0 / 1.5.2, .NetCore framework, jQuery. I am believing in quality work and achieving deadlines. Also like to work on new technologies and quick learner.
This entry was posted in Uncategorized and tagged . Bookmark the permalink.

27 Responses to MS Sql Server: Stored procedures vs. functions

  1. Bhavesh says:

    This article is good to get some diff between UDF and SP.

  2. Svetoslav says:

    I will add …

    I won’t agree on that
    CREATE FUNCTION

    Procedures are created as follows
    CREATE PROCEDURE

    One cannot call a stored procedure from a function.

  3. Vijay Modi says:

    Thanks buddy. I am very thankful to you.

  4. Maximus says:

    I would like to see a continuation of the topic

  5. raghu says:

    it is not good

  6. Vijay Modi says:

    Hi Raghu,

    Let me know what is the problem in this article. So that I can update it. Thanks for nice comment.

    Regards,
    Vijay Modi

  7. Harish says:

    Well I have a simple question are both stored procedure and function pre compiled sql server objects.

  8. Vijay Modi says:

    Hi Harish,

    MS Sql Server: Both stored procedures and user-defined functions are precompiled.

    Regards,
    Vijay Modi

  9. Rob says:

    Hmm…seems very similar to the article posted a few years earlier by Barrie Sosinsky.

    http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1063700,00.html?

  10. Vijay Modi says:

    Thank you for sending me the link.

  11. rax says:

    seems you copied the article from http://www.experts-exchange.com/Databases/Q_20958686.html which was created on 2004 :-).

    Nice copy paste

  12. Vijay Modi says:

    Hi Rax,

    Thank you. I added the reference on which I read this article. I was like that artilce and I also write the reference it just below the article. You can check on the following link.
    http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1063700,00.html

    Thankx for comment,

  13. Brett says:

    Rob’s point is worth repeating. Why plagiarise someone elses article (typos and all!). You are just causing confusion by making it appear that a 5 year old article was written in 2007.

    Please just post the link in future or write something original.

  14. Vijay Modi says:

    Brett,

    Sometimes someone’s article can be usefule to someone. And just for this I have added it here. Let me know if you have any concern with this.

    Regards,
    Vijay Modi

  15. AlbanianTodd says:

    It seems that developers in India love to fill up the internet with useless articles. Do you need to do this because of intense job competition?

  16. Vijay Modi says:

    Hi AlbanianTodd,

    Thanks for your comments. Indian are the first in Software Developement all over the World. Why do you know? They like to share knowledge. They believe to keep some important information and so others can also know it. I think you are from Canada. I like to keep some good articles on my blog like we are keeping some good toys & flower plant in our home even we have not made it. Some articles you can read which you have never read on another blogs. I am writing this because you told me about my country.

    Thanks,
    Vijay Modi

  17. AlbanianTodd says:

    You are right, the Indian developers in North America are excellent.

  18. Vijay Modi says:

    Hi AlbanianTodd,

    Actually if you can visit India, you will find the person are very honest to their work specially in our IT field(Software Developement). We indian developing different types of softwares. Even North America’s indian developers taught the developement in india and then they shifted there. Thats totally their personal nature. Hey What r u doing there? Nice to see your comments once more. 🙂

    Best Regards,
    Vijay Modi

  19. Virender Kumar says:

    Its a good artical.. for over all perspective.. but it can be more useful if some more points and some real things to be includes…

    some points need to be clear with some examples…

  20. umankumar says:

    thanks man it was usefull 🙂

  21. Raghav says:

    Dear Modi,

    Correct yourself Sql Functions(UDFs) are not pre compiled. OK

  22. gp says:

    Since Sql Server 7.0 Both UDFs AND Stored Procedures are NOT precompiled.

    After the first run they are completely compiled stored in de cache en the second run Sql Server uses the compiled cached version.

    Sql Server uses the same method for dynamic Sql.

    That why stored procedures are not quicker than dynamic sql when using paramters.

  23. Jason Yousef says:

    Hello, thanks for the article, but the info really are not enough for that topic.

    Hope you can add more Genuine info to the topic.

    Thanks
    Jason Yousef

  24. panda says:

    Hello, I think your website might be having browser compatibility issues.

    When I look at your blog site in Chrome, it looks fine but when opening in Internet Explorer, it has some overlapping.
    I just wanted to give you a quick heads up! Other then that, excellent blog!

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s