What is the difference between Stored Functions and Views in DB?

Question

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?

Solution 1

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

Solution 2

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.

Comments

[a_horse_with_no_name]

Selecting from a view is not slower than running the equivalent stored SELECT.

[a_horse_with_no_name]

You also can have functions that you can select from (at least in Oracle and others, not 100% sure about SQL Server)

[Elias Hossain]

So far I know, stored procedure is faster then View, am I wrong? Thanks for your time,

[a_horse_with_no_name]

No, that assumption (as a general rule) is wrong - at least for Oracle, but I'm quite sure for SQL Server as well.

[Elias Hossain]

Hello @a_horse_with_no_name, would you please see here: stackoverflow.com/questions/1603853/… , thanks for your time.

[idstam]

Yes I know, it's more of a mental distinction.

[a_horse_with_no_name]

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).

[Elias Hossain]

Yes, this is the point how SP is implemented! In my point of view, we always look for better! Thanks for your valuable time.

[theateist]

but I talked about Functions and not Procedures.

[Hasib]

i thought that you meant store-procedure, however please see the updated answer,thanks.

[Dylan Czenski]

also a view can't pass in parameters, SP and SF can. Also, PL/SQL has SP as well.