locked
Stored Procedure vs Function. RRS feed

  • Question

  • I don't see any recent discussion about Stored procedures vs Functions in SQL. 

    My question.  If i am simply getting data, not doing any updates to the data, is there a difference in the efficiency of the System that would make using a function more efficient then using a stored procedure.

    Here is some context.

    I inherited a project that uses stored procedures to get the data.  A limitation from the projects technology is that the application itself HAS to use Stored Procedures to get data.  The current design is that the stored procedures almost always call functions to do all the work of the actual query to get the data. The database literally has over 1000 tables and some tables have multiple billions of rows so efficiency matters.

    Thursday, May 14, 2020 5:15 PM

All replies

  • Stored procedures in SQL are easier to create and functions have a more rigid structure and support less clauses and functionality. By the other hand, you can easily use the function results in T-SQL. We show how to concatenate a function with a string. Manipulating results from a stored procedure is more complex.

    In a scalar function, you can return only one variable and in a stored procedure multiple variables. However, to call the output variables in a stored procedure, it is necessary to declare variables outside the procedure to invoke it.

    In addition, you cannot invoke procedures within a function. By the other hand, in a procedure you can invoke functions and stored procedures.

    here is blog give you good start https://www.sqlshack.com/functions-vs-stored-procedures-sql-server/


    http://uk.linkedin.com/in/ramjaddu

    Thursday, May 14, 2020 5:26 PM
  • It depends on exactly what the function is doing and how it is used.  Without seeing the code it is impossible to make a recommendation.

    Functions have limitations on what they can and cannot do. Stored Procs generally have more flexibility.  There are pros and cons to each method.

    • Proposed as answer by Naomi N Friday, May 15, 2020 4:52 PM
    Thursday, May 14, 2020 6:35 PM
  • I would not assume that it is more efficient to call a function directly versus calling a stored procedure that calls a function. 

    And you say that it has to call stored procedures so direct functions are not an option. So why are you asking? And unless you have hard data to that shows how your change will improve the system it is best to leave it alone. 

    I work with a guy that always says stuff like "that query is awful. i should refactor it". Mind you, nobody has complained about it running long, and it is not causing any problems. Then de never thoroughly runs before and after performance analysis to see if his rewrite actually increases performance. So about 40% of the time his changes makes things worse, and about 20% of the time he breaks something. Guess I am just cautious. And the way you explain the system makes me think that you should be cautious too.

    Thursday, May 14, 2020 10:07 PM
  • Hi Michael Fetros, 

    Thank you for your issue . 

    Firstly , Stored procedures and Functions are different . 

    1.The function must return a value but in Stored Procedure it is optional. Even a procedure can return zero or n values.
    2.Functions can have only input parameters for it whereas Procedures can have input or output parameters.
    3.Functions can be called from Procedure whereas Procedures cannot be called from a Function.
    4.The procedure allows SELECT as well as DML(INSERT/UPDATE/DELETE) statement in it whereas Function allows only SELECT statement in it.
    5.Procedures cannot be utilized in a SELECT statement whereas Function can be embedded in a SELECT statement.Stored Procedures cannot be used in the SQL statements anywhere in the WHERE/HAVING/SELECT section whereas Function can be.
    6.Functions that return tables can be treated as another rowset. This can be used in JOINs with other tables.
    7.Inline Function can be though of as views that take parameters and can be used in JOINs and other Rowset operations.
    8.An exception can be handled by try-catch block in a Procedure whereas try-catch block cannot be used in a Function.
    9.We can use Transactions in Procedure whereas we can't use Transactions in Function.

    If you think these difference could not affect the use of your requirements,you would like to know which one have a better performance. I will advice to do a try . 

    Here is an article which will share you an example to compare performance with stored procedures with table valued functions and scalar functions, please check Functions and stored procedures comparisons in SQL Server

    In different requirement , you might get the different conclusion.Also, as Tom Phillips said 'It depends on exactly what the function is doing and how it is used. '. Please do a test by your self , and you will get the result . 

    Best Regrads,

    Rachel


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Friday, May 15, 2020 5:45 AM