What is the difference between a stored procedure and a view

Question

I am confused about a few points    What is the difference between a stored procedure and a view  When should I use stored procedures  and when should I use views  in SQL Server  Do views allow the creation of dynamic queries where we can pass parameters   Which one is the fastest  and on what basis is one faster than the other  Do views or stored procedures allocate memory permanently  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

User · Answer

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.

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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

User · Answer

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   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   The depends on what you do   The depends on the database  Simple views just run the query and filter the result  But databases like Oracle allow to create a  quot materialized quot  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    I don t understand what you re talking about

User · Answer

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

User · Answer

In addition to the above comments  I would like to add few points about Views    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  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

User · Answer

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

User · Answer

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 gt 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

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

Examples related to sql

Examples related to sql-server-2005

Examples related to stored-procedures

Examples related to views