SQL Server - stored procedure and user defined
functions - Feb 27, 2010 at 11:50 AM by Shuchi Gauri
Since functions are not pre-compiled, is there any performance gain from using
user-defined functions as opposed to stored procedures?
User defined functions are pre-optimized and compiled and so are stored
procedures. User defined functions however, may have an overhead compared to a
stored procedure making a performance hit instead of gain.
SQL Server - stored procedure and user defined
functions - May 05, 2009 at 22:00 PM by Rajmeet Ghai
Since functions are not pre-compiled, is there any performance gain from using
user-defined functions as opposed to stored procedures?
Stored procedure performs better in comparison to user defined functions.
Stored procedures are precompiled and executed on the database server. This
decreases the network traffic. Since the stored procedure has direct access to
the data it needs to manipulate, it reduces the overhead to transport data from
server. UDF are not pre compiled and hence the results are not stored.
SQL Server - stored procedure and user defined
functions - June 21, 2009 at 09:00 AM by Amit Satpute
User-defined functions are pre-optimized and compiled similarly to stored
procedures. But even then, they have more overhead than the corresponding
stored procedures.
However, there some reasons to rewrite some stored procedures as user-defined
functions:
-
As user-defined function can act like tables, the developers can break complex
logic into shorter code blocks.
-
Stored procedure can also be rewritten as a user-defined function to be able to
invoke a stored procedure directly from within a query.
Also read
Answer - Stored procedures provide performance benefits through
local storage, precompiling the code, and caching......
Answer - Database users can have permission to execute a stored
procedure without being......
Answer - Views can be created referencing tables and views only
in the current database.......
|
