[sql] What is the difference between a stored procedure and a view?

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.

In addition to the above comments, I would like to add few points about Views.

1. Views can be used to hide complexity. Imagine a scenario where 5 people are working on a project but only one of them is too good with database stuff like complex joins. In such scenario, he can create Views which can be easily queried by other team members as they are querying any single table.
2. Security can be easily implemented by Views. Suppose we a Table Employee which contains sensitive columns like Salary, SSN number. These columns are not supposed to be visible to the users who are not authorized to view them. In such case, we can create a View selecting the columns in a table which doesn't require any authorization like Name, Age etc, without exposing sensitive columns (like Salary etc. we mentioned before). Now we can remove permission to directly query the table Employee and just keep the read permission on the View. In this way, we can implement security using Views.

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.

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.

A view is a simple way to save a complex SELECT in the database.

A store procedure is used when simple SQL just isn't enough. Store procedures contain variables, loops and calls to other stored procedures. It's a programming language, not a query language.

1. Views are static. Think of them as new tables with a certain layout and the data in them is created on the fly using the query you created it with. As with any SQL table, you can sort and filter it with WHERE, GROUP BY and ORDER BY.

2. The depends on what you do.

3. The depends on the database. Simple views just run the query and filter the result. But databases like Oracle allow to create a "materialized" view which is basically a table which is updated automatically when the underlying data of the view changes.

   A materialized view allows you to create indexes on the columns of the view (especially on the computed columns which don't exist anywhere in the database).

4. I don't understand what you're talking about.

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

Main difference is that when you are querying a view then it's definition is pasted into your query. Procedure could also give results of query, but it is compiled and for so faster. Another option are indexed views..

1. A VIEW is a dynamic query where you can use a "WHERE"-Clause
2. A stored procedure is a fixed data selection, which returns a predefined result
3. Nor a view, nor a stored procedure allocate memory. Only a materialized view
4. A TABLE is just one ENTITY, a view can collect data from different ENTITIES or TABLES

@Patrick is correct with what he said, but to answer your other questions a View will create itself in Memory, and depending on the type of Joins, Data and if there is any aggregation done, it could be a quite memory hungry View.

Stored procedures do all their processing either using Temp Hash Table e.g #tmpTable1 or in memory using @tmpTable1. Depending on what you want to tell it to do.

A Stored Procedure is like a Function, but is called Directly by its name. instead of Functions which are actually used inside a query itself.

Obviously most of the time Memory tables are faster, if you are not retrieveing alot of data.

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.