Understanding Stored Procedures and User Defined Functions

SQL Server

Stored Procedure

A Stored Procedure is a pre-compiled object stored in a database. It can be easily explained as a batch of code saved so it may be easily reused. Since a standard script is temporary in nature, you can save the code within a Stored Procedure so that we can execute it multiple times.

When the CREATE procedure statement is executed, your server compiles stored procedure and save it as a database object. Compilation of code is done once and every time compiled code is executed. The name of your procedure is stored in the sysobjects table, and code that creates your procedure is stored in the syscomments table.

User Defined Functions

We can also create functions to store a set of T-SQL statements permanently. These are called User Defined Functions (UDF). A UDF accepts a parameter, performs an action, and returns the result. A UDF can be scalar, which accepts a single parameter and returns a single data value of the type specified. It can also be table-valued which accepts a parameter and returns a table as an output.

Benefits

  • 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.
Advertisements

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