CODEWITHSUNDEEP
sqlt-sqlstored-proceduresview
EndangeringSpecies
EndangeringSpecies

Reputation: 1594

Is there any benefit to using a TSQL view vs the equivalent stored procedure?

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?

Upvotes: 3

Views: 2490

Answers (4)

Bogdan Bogdanov
Bogdan Bogdanov

Reputation: 1723

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.

Upvotes: 0

Andy Wilson
Andy Wilson

Reputation: 1383

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.

Upvotes: 1

Baaju
Baaju

Reputation: 2010

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.

Upvotes: 2

Chris Kooken
Chris Kooken

Reputation: 33880

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.

Upvotes: 7

Related Questions