3

Off the top of my head, I know one major disadvantage of a view vs stored procedure. If stored procedure A references stored procedure B and B gets changed, A will work correctly. But if view A references view B and B gets changed, A will need to be updated to work correctly. IMHO this view behavior just sucks and opens the door to all sorts of subtle bugs.

Well, so how about advantages? Why do people use views at all if they could just always use equivalent stored procedures with names prefixed with vw for clarity?

Jon Schneider
  • 25,758
  • 23
  • 142
  • 170
EndangeringSpecies
  • 1,564
  • 1
  • 17
  • 39
  • "if view A references view B and B gets changed, A will need to be updated to work correctly" Only if you use `*` AFAIK? (I assume you are talking about the need to run `sp_refreshview`?) – Martin Smith May 04 '11 at 19:05
  • @Martin, I don't know, maybe you are right. I did not get into such details about which changes do and do not get noticed. Thanks for telling me about sp_refreshview; the way I did it so far was, I had a query incorporating definition queries for all views VA dependent on view VB (on separate lines) and then I just ran this query in SQL Studio manually. – EndangeringSpecies May 04 '11 at 20:22
  • The "benefit" of using a `VIEW` is that it is a SQL table, whereas the result of a query (say returned by a stored procedure) is a... well, I'm not sure what it is. It's like a table but isn't e.g. its column names can be duplicated and/or 'blank', its rows can be ordered, etc. It's often called a resultset but it's not a set either (neither is a SQL table a set -- including `VIEW`s -- e.g. can contain duplicate rows, has left-to-right column ordering, etc). – onedaywhen May 05 '11 at 09:28
  • Related: http://stackoverflow.com/questions/3773277/stored-procedures-vs-views – Jon Schneider May 19 '15 at 19:09

4 Answers4

7

You cant filter a stored proc without passing in parameters. In a view, you can query it just as you would a table, adding any where clauses as necessary.

Chris Kooken
  • 32,730
  • 15
  • 85
  • 123
  • right, just came to say the same thing. Apparently using stored procedures in queries is disallowed. – EndangeringSpecies May 04 '11 at 19:06
  • @EndangeringSpecies, you can insert stored procedure result into a table, though: `INSERT INTO Table EXEC MyStoredProcedure`, just make sure the columns match. – Gabrielius Oct 18 '16 at 11:26
2

SQL Views are used for lot other purposes.

1) Hide a few columns from the original tables and create a view allot permissions only to the view for certain set of users. I guess this is one major use of a view.

2) Combine 2 or more tables, have a derived column

3) Indexed views where unlike normal views have a allocated memory in contrast to that of a normal view where the execution plan shows the usage of main table. So you could actually use the indexed views work efficiently without referencing the main table.

Baaju
  • 1,992
  • 2
  • 18
  • 22
1

If it's a choice between "select * from vwMyView" and "exec MyProc()", there's not a lot of difference. Both will return a result set that you can use however you'd like. One thing to note is that if you use a view, it can be joined to other tables, which may or may not apply to your situation.

If you want/need to filter the result set, you would want to use a view, as it's simple to add a where clause, where, with a proc, you would need to pass in parameters.

If you want/need to use an existing proc as part of your source, you would need to use a stored procedure, as a view cannot reference a stored procedure.

The "subtle bugs" you mention can occur with either method, though. If you have a view A reference view B, and the query that materializes view B changes (without changing the number of columns returned), anything that depends on view A may break. The same is true of the stored procs: if proc B changes the way its result set is created, proc A may no longer work correctly.

In general, you need to be careful when you have views reference views and procs referencing procs. In addition to the aforementioned issue, you can introduce some pretty serious performance problems.

Andy Wilson
  • 1,383
  • 9
  • 15
  • are you sure about your claim about change to stored procedure PB requiring update to procedure PA? Although, now that I think about it, I didn't actually test changing stored procedure PB. I think what I did was have procedure PA reference view VB, then I slightly changed view VB and procedure PA worked correctly. FWIW – EndangeringSpecies May 04 '11 at 20:19
  • I guess it depends on what you mean by "change". A view is nothing but a query, so if View A is relying on a column in View B, and you remove that column in View B, View A will be broken. The same is true of procs. If Proc A is relying on a column from a result set returned by Proc B, and you change Proc B to no longer return than column, Proc A is now broken. Think of the view columns or proc result set(s) as the public interface. As long as you don't change the public interface, you won't "break" consumers, but you may alter their behavior in an undesirable way. – Andy Wilson May 05 '11 at 18:20
  • that was not what I meant. At least in the case of views, if you don't update vwConsumer after changing vwProducer ever so slightly, vwConsumer will still execute as if vwProducer never changed. You can put "TOP 1 PERCENT" into vwProducer and vwConsumer will still be getting all the rows as before. – EndangeringSpecies May 05 '11 at 21:53
0

From my point of view there are 2 major topics:

  1. Caches plan of Stored procedure - this will benefit the execution of SP, although if the view is executed often its plan will also be in the cash.

  2. View can be used in JOINs which can not be done by SP.

Bogdan Bogdanov
  • 1,707
  • 2
  • 20
  • 31