Stored Procedures VS. Views

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

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.

Stored Procedures Vs. Views

Well, I'd use stored proc for encapsulation of code and control permissions better.

A view is not really encapsulation: it's a macro that expands. If you start joining views pretty soon you'll have some horrendous queries. Yes they can be JOINed but they shouldn't..

Saying that, views are a tool that have their place (indexed views for example) like stored procs.

Which one is best View or Stored procedure in sql server for data access purpose

Views and stored procedures serve entirely different purposes. Views are a convinient way to refer to a complex relational set (such as one that joins across many tables) as a flat table without actually forcing the data to be manifested. You use a view to clean up SQL code. Your stored procedures could call views. Views are often used for permission control. You can grant a database user access to a view without granting them access to the underlying tables. This grants the user column level permissions on the columns in the view which is a far more granular method for permission control than granting access to whole tables.

Stored procedures are used to keep often used functionality together as a unit. To be honest, SPs are falling out of favor among many programmers. While you are correct that SPs have their execution plans cached, dynamic SQL has had execution plan caching since SQL Server 2000 (I believe that's the correct version). The only speed gain you're going to get by going with SPs is by sending less data over the network, and that's going to be extremely minimal. SPs tend to make code more brittle and require changes to the DB to occur when application changes don't really warrant it. For example, if you just wanted to change the conditions for which rows you're selecting. Using SPs, you're going to have to roll changes out to the application and the database code. If you're using dynamic SQL or an ORM tool, you only need to make changes to the application which simplifies deployment. There is absolutely a time and place for SPs, but they don't need to be your only method for interacting with the database.

Also, if you're worried about performance, you can materialize views which reduces the need to repeatedly query the underlying tables. This could greatly enhance your performance if you feel the need to add the extra overhead on inserts/updates that materializing views induces.

SQL-Server Performance: What is faster, a stored procedure or a view?

Stored Procedures (SPs) and SQL Views are different "beasts" as stated several times in this post.

If we exclude some [typically minor, except for fringe cases] performance considerations associated with the caching of the query plan, the time associated with binding to a Stored Procedure and such, the two approaches are on the whole equivalent, performance-wise. However...

A view is limited to whatever can be expressed in a single SELECT statement (well, possibly with CTEs and a few other tricks), but in general, a view is tied to declarative forms of queries. A stored procedure on the other can use various procedural type constructs (as well as declarative ones), and as a result, using SPs, one can hand-craft a way of solving a given query which may be more efficient than what SQL-Server's query optimizer may have done (on the basis of a single declarative query). In these cases, an SPs may be much faster (but beware... the optimizer is quite smart, and it doesn't take much to make an SP much slower than the equivalent view.)

Aside from these performance considerations, the SPs are more versatile and allow a broader range of inquiries and actions than the views.

MySQL: Views vs Stored Procedures

In my opinion, Stored Procedures should be used solely for data manipulation when the same routine needs to be used amongst several different application or for ETL between databases or tables, nothing more. Basically, do as much in code as you can until you run into the DRY principle or what you are doing is simply moving data from one place to another within the DB.

Views can be used to provide an alternate or simplified "view" into the data. As such, I would go with a view as you are not really manipulating the data as much as finding a different method of displaying it.

Comparing The Performance Of Indexed Views And Stored Procedures In SQL Server

An indexed view can be regarded like a normal table - it's a materialized collection of rows.

So the question really boils down to whether or not a "normal" query is faster than a stored procedure.

If you look at what steps the SQL Server goes through to execute any query (stored procedure call or ad-hoc SQL statement), you'll find (roughly) these steps:

1. syntactically check the query
2. if it's okay - it checks the plan cache to see if it already has an execution plan for that query
3. if there is an execution plan - that plan is (re-)used and the query executed
4. if there is no plan yet, an execution plan is determined
5. that plan is stored into the plan cache for later reuse
6. the query is executed

The point is: ad-hoc SQL and stored procedures are treatly no differently.

If an ad-hoc SQL query is properly using parameters - as it should anyway, to prevent SQL injection attacks - its performance characteristics are no different and most definitely no worse than executing a stored procedure.

Stored procedure have other benefits (no need to grant users direct table access, for instance), but in terms of performance, using properly parametrized ad-hoc SQL queries is just as efficient as using stored procedures.

Using stored procedures over non-parametrized queries is better for two main reasons:

• since each non-parametrized query is a new, different query to SQL Server, it has to go through all the steps of determining the execution plan, for each query (thus wasting time - and also wasting plan cache space, since storing the execution plan into plan cache doesn't really help in the end, since that particular query will probably not be executed again)

• non-parametrized queries are at risk of SQL injection attack and should be avoided at all costs

Now of course, if you're indexed view can reduce down the number rows significantly (by using a GROUP BY clause) - then of course that indexed view will be significantly faster than when you're running a stored procedure against the whole dataset. But that's not because of the different approaches taken - it's just a matter of scale - querying against a few dozen or few hundred rows will be faster than querying against 200'000 or more rows - no matter which way you query.

---

Related Topics