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