CODEWITHSUNDEEP
sqlsql-server
Shikha
Shikha

Reputation: 221

Advantages of Userdefined functions over Stored Procedures

I have some doubt regarding user defined functions. I would like to know why / when to use functions.

What are the advantages of functions over stored procedure?

Researching via google I have seen articles suggesting:

The only advantage of function is we can use function as inline queries.

I can get the same result with stored procedure by using temporary tables, but i need to know which scenario to use functions compared to stored procedure. I need to know why we need UDf , when most of the functionalities provided by UDF can be done by Stored procedure. Can any one guide me over this.

Upvotes: 5

Views: 11133

Answers (4)

Reshan Maduka
Reshan Maduka

Reputation: 291

Advantage of Mysql Stored Procedure

  1. Multiple applications are running in multiple environment and need to use the same database. By using stored procedure you can make your business logic independent of programming language.

  2. When security is main concern use of stored procedure is vital. By doing your operation through the database you can log your all performed action. Banking site is the best example.

  3. If you are using stored procedure then you do not have table access directly which is one more way to secure the data and transaction.

  4. Stored procedure increases performance of your application sometime

  5. If your application is big or your database server on remote system then by using stored procedure you can decrease the traffic between your database server and application server.

  6. Since stored procedure is written in your database server and application call it sepratly then the degree of re-usability.

Upvotes: 0

Frank Kalis
Frank Kalis

Reputation: 1352

The main "disadvantage" of user-defined functions is that they are called for each row. So, if you have such a function in the SELECT list and you're operating on larger sets, there are good chances that your performance will suffer.

Upvotes: 2

AdaTheDev
AdaTheDev

Reputation: 147244

The main difference (advantage) is that you can call functions inline unlike stored procedures e.g.

SELECT dbo.fxnFormatName(FirstName, LastName) AS FormattedName
FROM MyTable

SELECT * 
FROM dbo.fxnTableReturningFunction() x

User defined functions can return TABLE type data and then the function can then be called within a query as demonstrated above. With a sproc, you'd have to execute it and store the results into a temporary table in order to then manipulate/query the resultset further.

On the flip side, yes you are limited as to what you can do in a function. e.g. you can't use dynamic sql, and pre-SQL 2005 you can't use non-deterministic functions like GETDATE() within a function.

An example of when you may want to use functions, is to wrap up common "formatting" functionality as shown in the first example above - rather than repeat the logic to format a first and last name into one in every query, you wrap it in a function and call that everywhere. Typically I'd recommend leaving the formatting up to the UI but it's a simple example of where/why you might use.

Also, it can often be nicer to not have to create temp tables to hold results from a sproc in order to query it further. If the sproc changes and returns more columns, you'd also need to change everywhere that loads the results into a temp table to synch the schema of the table table it uses to hold the results with the new schema returned. You don't have this problem with the function approach as there is no temp table to be maintained.

Upvotes: 3

Matt Whitfield
Matt Whitfield

Reputation: 6574

There are three types of functions: Scalar, Inline Table and Table Valued. Generally speaking, Scalar & Table Values functions can lead to performance problems, seeing as the Query Optimiser doesn't do very well at optimisation of the use of those types of functions. The performance of Inline Table function is just fine, however.

There is a Connect request to create a new type of scalar function here: The Scalar Expression function would speed performance...

I hope that people do vote for that one, because it would improve performance greatly by allowing the query optimiser to inline functional expressions and take advantage of statistics etc just as it would for a normal query.

Upvotes: 2

Related Questions