Thursday, 6 September 2012

Difference between User-defined functions and stored procedure in SQL Server

What is Stored Procedures

A stored procedure is a group of Transact-SQL statements compiled into a single execution plan. The stored procedure mechanism to simplify the database development process by grouping Transact-SQL statements into manageable blocks.

Stored procedures can also improve performance. Many tasks are implemented as a series of SQL statements. Conditional logic applied to the results of the first SQL statements determines which subsequent SQL statements are executed. If these SQL statements and conditional logic are written into a stored procedure, they become part of a single execution plan on the server. The results do not have to be returned to the client to have the conditional logic applied; all of the work is done on the server. 

Benefits of Stored Procedures
  • Precompiled execution : SQL Server compiles each stored procedure once and then reutilizes the execution plan. This results in tremendous performance boosts when stored procedures are called repeatedly.

  • Reduced client/server traffic : If network bandwidth is a concern in your environment, you'll be happy to learn that stored procedures can reduce long SQL queries to a single line that is transmitted over the wire.

  • Efficient reuse of code and programming abstraction : Stored procedures can be used by multiple users and client programs. If you utilize them in a planned manner, you'll find the development cycle takes less time.

  • Enhanced security controls : You can grant users permission to execute a stored procedure independently of underlying table permissions.

What is User-defined functions

Functions in programming languages are subroutines used to encapsulate frequently performed logic. Any code that must perform the logic incorporated in a function can call the function rather than having to repeat all of the function logic.

SQL Server supports two types of functions:
  • Built-in functions : Operate as defined in the Transact-SQL Reference and cannot be modified. The functions can be referenced only in Transact-SQL statements using the syntax defined in the Transact-SQL Reference.  

  • User-defined functions : Allow you to define your own Transact-SQL functions using the CREATE FUNCTION statement. User-defined functions take zero or more input parameters, and return a single value. Some user-defined functions return a single, scalar data value, such as an int, char, or decimal value.

Benefits of User-defined functions
  • They allow modular programming : You can create the function once, store it in the database, and call it any number of times in your program. User-defined functions can be modified independently of the program source code.

  • They allow faster execution : Similar to stored procedures, Transact-SQL user-defined functions reduce the compilation cost of Transact-SQL code by caching the plans and reusing them for repeated executions. This means the user-defined function does not need to be reparsed and reoptimized with each use resulting in much faster execution times. CLR functions offer significant performance advantage over Transact-SQL functions for computational tasks, string manipulation, and business logic. Transact-SQL functions are better suited for data-access intensive logic.

  • They can reduce network traffic :  An operation that filters data based on some complex constraint that cannot be expressed in a single scalar expression can be expressed as a function. The function can then invoked in the WHERE clause to reduce the number or rows sent to the client.

Difference between stored procedure and User-defined functions

Stored Procedure User-defined functions
Its may or may not return a value Function must return a value(scalar,inline table or multi statement table)
SP can create table but won’t return Table Variables Not returning output parameter but returns Table variables
SP can not be used with Select statement and have to use EXEC or EXECUTE Function can be used with Select statement
SP can be used to change server configuration(in terms of security-i.e. setting granular permissions of user rights) function can't be used for this
XML and output parameters can be passed to SP XML and output parameters can't be passed to functions
transaction related statement can be handled in SP it can't be in function
stored procedures can call a function or another SP similarly a function can call another function and a SP .The catch with function is that no user defined SP can be called.Only extended/system defined procs can be called

1 comment:

  1. 20 main differences between Stored procedures and Functions in Sql Server