5

I didn't undetstood the difference between Stored Functions and Views. Using Views in SELECT will execute the query and return the result, but Stored Functions do the same thing, don't they? So what is the difference? When I use Views and when Stored Functions?

theateist
  • 13,879
  • 17
  • 69
  • 109
  • Possible duplicate of: http://stackoverflow.com/questions/988373/difference-between-a-inline-function-and-a-view – Yaqub Ahmad Dec 02 '11 at 07:59

6 Answers6

7

View: A view is a virtual table. It does not physically exist. Rather, it is created by a query joining one or more tables. View returns a table.

Stored procedure: A stored procedure is a group of Transact-SQL statements compiled into a single execution plan.
stored procedures returns Output parameters,return codes (which are always an integer value), a result set for each SELECT statement contained in the stored procedure or any other stored procedures called by the stored procedure,a global cursor that can be referenced outside the stored procedure.
key benefits of stored procedure are Precompiled execution, reduced client/server traffic,efficient reuse of code, programming abstraction and enhanced security controls.

Update:

A stored function is a named PL/SQL Block which is similar to a procedure. The major difference between a procedure and a function is, a function must always return a value, but a procedure may or may not return a value. 1) Return Type: The header section defines the return type of the function. The return datatype can be any of the oracle datatype like varchar, number etc. 2) The execution and exception section both should return a value which is of the datatype defined in the header section

Hasib
  • 100
  • 1
  • 6
3

You can have a stored function return the same data a view would in most databases.

The distinction for me is that a function is executed and a view is selected from.

A view will behave as a table.

idstam
  • 2,848
  • 1
  • 21
  • 30
  • You also can have functions that you can select from (at least in Oracle and others, not 100% sure about SQL Server) –  Dec 02 '11 at 08:21
  • Yes I know, it's more of a mental distinction. – idstam Dec 02 '11 at 09:39
0

A view returns a specific pre-defined statement as exactly one result set.

A function returns a single values or a single result set. This however can differ from different types of database.

Several db implementations also have stored procedures where the result can be a single returned value, a result set or several result sets.

Asken
  • 7,679
  • 10
  • 45
  • 77
0

Getting simple (PLEASE start reading a book about SQL): A view looks like a table, so you can filter on the results and the filter will efficiently be part of the views execution, or do joins. A SP does not allow this, but a lot more logic. The rest... is in the documentation.

TomTom
  • 61,059
  • 10
  • 88
  • 148
0

These can never be compares, these have totally different approach.

A view is a output of a query ,and makes a virtual image of the table,and the input parameters are not accepted.

Main difference is that a Stored Procedure can alter your data, where as a view only returns it and I believe from a performance point of view, a stored procedure is better as it caches the execution plan and will run faster as a result.

storedprocedure/function is a group of sql statements that are pre-executed and it accepts the parameters.it reduces network traffic, gives faster performance, etc.

SQL Functions in programming languages are subroutines used to encapsulate frequently performed logic. these somewhat slow down the performance.

Check these SQL View, SQL Stored Procedures and SQL User-Defined Functions

My Opinion is that SQL Stored Procedure(Stored Functions) are much better to use because it provides custom manipulations on result set also.

Niranjan Singh
  • 18,017
  • 2
  • 42
  • 75
0

From my experiences I'm sharing to you my knowledge:

  • Don't use views
  • Better to use a stored procedure(it is compiled sql statement), you can use parametrized procedure as required.
  • Stored Function is collection of complied sql statement which is faster.

Note: Views is a SELECT statement( with/without JOIN) for a table which select data from table and if we again run a SELECT statement from VIEWS which provide slower result because the internal operation is as ( SELECT * FROM ( SELECT * FROM TargetTable ) )

So, its better to use Stored Function

Update:

Functions are computed values and cannot perform permanent environmental changed to SQL Server (i.e. no INSERT or UPDATE statements allowed).

A Function can be used inline in SQL Statements if it returns a scalar value or can be joined upon if it returns a result set.

Also please see here for performance comparison: SQL-Server Performance: What is faster, a stored procedure or a view?

Community
  • 1
  • 1
Elias Hossain
  • 4,410
  • 1
  • 19
  • 33
  • 1
    Selecting from a view is not slower than running the equivalent stored SELECT. –  Dec 02 '11 at 08:20
  • So far I know, `stored procedure` is faster then `View`, am I wrong? Thanks for your time, – Elias Hossain Dec 02 '11 at 08:34
  • No, that assumption (as a general rule) is wrong - at least for Oracle, but I'm quite sure for SQL Server as well. –  Dec 02 '11 at 08:44
  • Hello @a_horse_with_no_name, would you please see here: http://stackoverflow.com/questions/1603853/sql-server-performance-what-is-faster-a-stored-procedure-or-a-view , thanks for your time. – Elias Hossain Dec 02 '11 at 09:21
  • well that post just proves my point. If a procedure and a view are using the same statement, there is no performance difference. Of course there will be a performance difference if they are doing things differently (and the SP could slower or faster than the view depending on how the SP is implemented). –  Dec 02 '11 at 09:50
  • Yes, this is the point how SP is implemented! In my point of view, we always look for better! Thanks for your valuable time. – Elias Hossain Dec 02 '11 at 10:30