67

I have used both but what I am not clear is when I should prefer one over the other. I mean I know stored procedure can take in parameters...but really we can still perform the same thing using Views too right ?

So considering performance and other aspects when and why should I prefer one over the other ?

Vishal
  • 12,133
  • 17
  • 82
  • 128
  • 7
    You should include user-defined functions in your considerations. – John Saunders Sep 22 '10 at 20:27
  • Seems a duplicate of this one: [What are the pros and cons to keeping SQL in Stored Procs versus Code](http://stackoverflow.com/questions/15142/what-are-the-pros-and-cons-to-keeping-sql-in-stored-procs-versus-code), which is also a Community Wiki. – Hibou57 Aug 18 '13 at 10:23

7 Answers7

36

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.

gbn
  • 422,506
  • 82
  • 585
  • 676
  • This answer was very much imp to me. But i am wondering, how to run views as i know how to run stored proc as exec spName.please guid me. – Freelancer May 09 '13 at 10:14
  • 2
    @Freelancer: `SELECT * FROM View`, Just like a table – gbn May 09 '13 at 10:15
21

The advantage of views is that they can be treated just like tables. You can use WHERE to get filtered data from them, JOIN into them, et cetera. You can even INSERT data into them if they're simple enough. Views also allow you to index their results, unlike stored procedures.

Matti Virkkunen
  • 63,558
  • 9
  • 127
  • 159
  • 3
    See my answer... ? The mentality is "we can have a view that does all this stuff for us", Then view join view join view = horrible query on base tables. Seen it, fixed it several times before. And answered questions on it too http://stackoverflow.com/search?q=user%3A27535+macro%2Bview – gbn Sep 22 '10 at 20:29
  • 1
    Yes, but is a view for encapsulation or for joining? JOINIng alone is not a good enough reason. If you have such simple views so that JOINing is not issue, why not use base tables? Views are abused way too often. – gbn Sep 22 '10 at 20:34
  • 2
    These arguments against VIEWs seem weak and rely on either broad generalizations or a bad query planner. Look at the opposite side of the coin, the critiques think using a Stored Proc to replace a VIEW is a good idea just because it *can't* be used in a JOIN.. This seems rather crazy. – Evan Carroll Sep 22 '10 at 20:56
  • 4
    @gbn: The problem you are talking about is no fault of views, it's just the unfortunate side-effect of people using the views without understanding the implications. Unfortunately, it seems that I see abuse of views far mor often than correct use of them. – Bennor McCarthy Sep 22 '10 at 21:25
  • “You can even INSERT data into them if they're simple though”: this may in return gives a rationale for the choice of stored procedure: this prevent insertion attempts (inserting in view may only insert in another table and not add anything in the target view, so inserting in a view, may be something one wish to disallow). – Hibou57 Aug 18 '13 at 08:27
15

A View is just like a single saved query statement, it cannot contain complex logic or multiple statements (beyond the use of union etc). For anything complex or customizable via parameters you would choose stored procedures which allow much greater flexibility.

It's common to use a combination of Views and Stored Procedures in a database architecture, and perhaps for very different reasons. Sometimes it's to achieve backward compatibility in sprocs when schema is re-engineered, sometimes to make the data more manipulatable compared with the way it's stored natively in tables (de-normalized views).

Heavy use of Views can degrade performance as it's more difficult for SQL Server to optimize these queries. However it is possible to use indexed-views which can actually enhance performance when working with joins in the same way as indexed-tables. There are much tighter restrictions on the allowed syntax when implementing indexed-views and a lot of subtleties in actually getting them working depending on the edition of SQL Server.

Think of Views as being more like tables than stored procedures.

Maarten Bodewes
  • 90,524
  • 13
  • 150
  • 263
TheCodeKing
  • 19,064
  • 3
  • 47
  • 70
13

The main advantage of stored procedures is that they allow you to incorporate logic (scripting). This logic may be as simple as an IF/ELSE or more complex such as DO WHILE loops, SWITCH/CASE.

Nasir
  • 10,935
  • 8
  • 31
  • 39
5

I correlate the use of stored procedures to the need for sending/receiving transactions to and from the database. That is, whenever I need to send data to my database, I use a stored procedure. The same is true when I want to update data or query the database for information to be used in my application.

Database views are great to use when you want to provide a subset of fields from a given table, allow your MS Access users to view the data without risk of modifying it and to ensure your reports are going to generate the anticpated results.

SidC
  • 3,175
  • 14
  • 70
  • 132
  • 5
    First, you shouldn't have MS Access users. Second, proper security and permissions should prevent them from modifying data. Not views. – CaffGeek Sep 22 '10 at 20:29
4

Views are useful if there is a certain combination of tables, or a subset of data you consistently want to query, for example, an user joined with its permissions. Views should in fact be treated as tables.

Stored procedures are pieces of sql code that are 'compiled', as it where, to run more optimally than a random other query. The execution plan of sql code in a stored procedure is already built, so execution runs slightly smoother than that of an ordinary sql statement.

Joachim VR
  • 2,320
  • 1
  • 15
  • 24
0

Two rationales.

Use stored procedure instead of view if you don't want insertion to be possible. Inserting in a view may not give what it seems to do. It will insert in a table, a row which may not match the query from the view, a row which will then not appear in the view; inserted somewhere, but not where the statement make it seems.

Use a view if you can't use the result of a stored procedure from another stored procedure (I was never able to make the latter works, at least with MySQL).

Hibou57
  • 6,870
  • 6
  • 52
  • 56