SQL Server Performance

Which one is better Option? Sp Vs. Function

Discussion in 'SQL Server 2005 General Developer Questions' started by Siona, Mar 2, 2009.

  1. Siona New Member

    Dear Friends,
    In Sql Server which one is better option for a reusable codes. FUNCTION or Stored Procedures..
  2. FrankKalis Moderator

    I don't think there is just one true answer to this question. This really depends on your specific case. Every object type has its rightful place.
  3. Sandy New Member

    I can only say its depends on you and how you need the results. for example if you need a single result output which based on any calculations then you can go with UDF but when you need a batch of query execute and gives you a result then you can go with a Stored Procedure.
    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 (SPs) use EXECUTE or EXEC to run.
  4. Siona New Member

    Thanks for explain me clearly…
    Hope I am not taking your more time. I am a beginner.

    Like your way of approach.[:D]
  5. Sandy New Member

    You are most welcome...

  6. Adriaan New Member

    I notice that the original poster mentions "reusable code". In my humble experience, T-SQL shows its best form when stored procedures are created with a specific goal in mind.
    T-SQL has its own best practices, which overlap with but also differ from, best practices in general programming.
    The unique selling point of T-SQL is its set-based approach to data, compared to general programming which can handle only one bit of data at a time. Once you move away from the set-based approach in database programming, you will probably pay for that in performance. (And you will run into situations where set-based isn't an option - that's just the way it is.)
    The first step on the road away from the set-based approach is often the use of UDFs, so use them wisely.
  7. satya Moderator

    I would say what is your favourite to work upon, in some cases functions come handy than stored procedure.
    You will often hear the term Best Practices used in Application Architecture. The danger of such a term is that it gives one a sense that there is no need to question a practice because it is always best. Just follow Best Practices outlined by an authority in the subject matter and things will magically fall into place. So it is your own practice that if you can obtain the results in timely manner then its a best practice for you.
  8. Siona New Member

    Thank You.

Share This Page