What is the difference between a stored procedure and a view?

Question

I am confused about a few points:

1. What is the difference between a stored procedure and a view?

2. When should I use stored procedures, and when should I use views, in SQL Server?

3. Do views allow the creation of dynamic queries where we can pass parameters?

4. Which one is the fastest, and on what basis is one faster than the other?

5. Do views or stored procedures allocate memory permanently?

6. What does it mean if someone says that views create a virtual table, while procedures create a materials table?

Please let me know about more points, if there are any.

Solution 1

A view represents a virtual table. You can join multiple tables in a view and use the view to present the data as if the data were coming from a single table.

A stored procedure uses parameters to do a function... whether it is updating and inserting data, or returning single values or data sets.

Creating Views and Stored Procedures - has some information from Microsoft as to when and why to use each.

Say I have two tables:

• tbl_user, with columns: user_id, user_name, user_pw
• tbl_profile, with columns: profile_id, user_id, profile_description

So, if I find myself querying from those tables A LOT... instead of doing the join in EVERY piece of SQL, I would define a view like:

CREATE VIEW vw_user_profile
AS
  SELECT A.user_id, B.profile_description
  FROM tbl_user A LEFT JOIN tbl_profile B ON A.user_id = b.user_id
GO

Thus, if I want to query profile_description by user_id in the future, all I have to do is:

SELECT profile_description FROM vw_user_profile WHERE user_id = @ID

That code could be used in a stored procedure like:

CREATE PROCEDURE dbo.getDesc
    @ID int
AS
BEGIN
    SELECT profile_description FROM vw_user_profile WHERE user_id = @ID
END
GO

So, later on, I can call:

dbo.getDesc 25

and I will get the description for user_id 25, where the 25 is your parameter.

There is obviously a lot more detail, this is just the basic idea.

Solution 2

Plenty of info available here

Here is a good summary:

A Stored Procedure:

• Accepts parameters
• Can NOT be used as building block in a larger query
• Can contain several statements, loops, IF ELSE, etc.
• Can perform modifications to one or several tables
• Can NOT be used as the target of an INSERT, UPDATE or DELETE statement.

A View:

• Does NOT accept parameters
• Can be used as building block in a larger query
• Can contain only one single SELECT query
• Can NOT perform modifications to any table
• But can (sometimes) be used as the target of an INSERT, UPDATE or DELETE statement.

Solution 3

A SQL View is a virtual table, which is based on SQL SELECT query. A view references one or more existing database tables or other views. It is the snap shot of the database whereas a stored procedure is a group of Transact-SQL statements compiled into a single execution plan.

View is simple showcasing data stored in the database tables whereas a stored procedure is a group of statements that can be executed.

A view is faster as it displays data from the tables referenced whereas a store procedure executes sql statements.

Check this article : View vs Stored Procedures . Exactly what you are looking for

Solution 4

First you need to understand, that both are different things. Stored Procedures are best used for INSERT-UPDATE-DELETE statements. Whereas Views are used for SELECT statements. You should use both of them.

In views you cannot alter the data. Some databases have updatable Views where you can use INSERT-UPDATE-DELETE on Views.

Solution 5

Mahesh is not quite correct when he suggests that you can't alter the data in a view. So with patrick's view

CREATE View vw_user_profile AS 
Select A.user_id, B.profile_description
FROM tbl_user A left join tbl_profile B on A.user_id = b.user_id

I CAN update the data ... as an example I can do either of these ...

Update vw_user_profile Set profile_description='Manager' where user_id=4

or

Update tbl_profile Set profile_description='Manager' where user_id=4

You can't INSERT to this view as not all of the fields in all of the table are present and I'm assuming that PROFILE_ID is the primary key and can't be NULL. However you can sometimes INSERT into a view ...

I created a view on an existing table using ...

Create View Junk as SELECT * from [TableName]

THEN

Insert into junk (Code,name) values 
('glyn','Glyn Roberts'),
('Mary','Maryann Roberts')

and

DELETE from Junk Where ID>4

Both the INSERT and the DELETE worked in this case

Obviously you can't update any fields which are aggregated or calculated but any view which is just a straight view should be updateable.

If the view contains more than one table then you can't insert or delete but if the view is a subset of one table only then you usually can.

View more solutions

Comments

[Siavoshkc]

I would ask when should I use a view instead of a stored procedure.

[NoviceToDotNet]

But i can join can join multiple tables in stored procedure as well where i don't have to give parameter.

[Patrick]

but WHY would you do that? what are you trying to accomplish? You can use a view AS a table... stored procedures are for DOING things... views are for making your life easier.

[Patrick]

Think of a view as a stored query so if you have two tables that you find you have to join a lot to get work done, you can create a view to work against so you don't have to join them all of the time.

[NoviceToDotNet]

I am not getting, please make little more description.

[Patrick]

if i need to return a data set from the server based on parameters... use a stored procedure called from your server side code. there are two different animals.

[NoviceToDotNet]

OK , if create a view then from view only i can get the data, but after all it have to refer the actual tables to get the data then where the difference lie in stored procedure and views

[NoviceToDotNet]

But after all in View i also have to put join to get data from the multiple tables.

[Patrick]

right but it STORES your view then... so that you can call it like a single table. that way you creat your join once, and any future uses reference the view directly which calls the underlying sql as if it WAS a table.

[NoviceToDotNet]

Still thinking that when the same task can be done by SP too, like if i create a sp and any body knowing about that sp can access the data.

[sksallaj]

Also Views shouldn't contain any "order by" or "top" clauses

[usefulBee]

@Patrick, this answer gives the impression that a View does not perform joins every single time it is being called, which is not true. Prove me wrong.

[Dot Net developer]

You can alter data in underlying table using Views. Views are updateable.

[Arsman Ahmad]

What does mean by "can NOT be used as the target of an INSERT, UPDATE or DELETE statement"? Can't we use INSERT,DELETE,UPDATE in Stored Procedure ?

[Mohammad Afrashteh]

I couldn't understand the differences! If the view stored the data of its query result then we could say the execution speed of view is more than SP! But we can do everything with view as well with SP!

[xayer]

"A view, does NOT accept parameters", it's not true? The video for example: youtube.com/watch?v=zK-mWjUxKpw

[Khurram W. Malik]

Views can perform modifications to base table/tables: csharp-video-tutorials.blogspot.com/2012/09/…

[Pulsara Sandeepa]

when we executing stored procedures we use exec or execute command is it not mandatory?

[Martijn]

@ArsmanAhmad to clarify: You CAN use INSERT, UPDATE and DELETE in a Stored Procedure. But the Stored Procedure can NOT be the target of INSERT, UPDATE or DELETE statements. For example: you cannot insert data into a Stored Procedure. A Stored Procedure is not a table of any kind, so you cannot INSERT data into it, UPDATE data in it or DELETE data in it.

[Arsman Ahmad]

@Martijn Thank you! It makes sense.

[Seb]

The views are updatable, but with certain conditions: you can update/insert data in one of the base tables which are in the from clause of the view.

[Siavoshkc]

Why a Stored Procedure can't be used as building block in a larger query?

[Dave Black]

This is not correct. A View is a stored SELECT statement and cannot accept parameters. A stored procedure is dynamic and can accept parameters. You can use WHERE clauses in both and you can query data from multiple tables in both.