Posted by Serge Rielau on 09/25/42 11:38

mooreit wrote:
> The purpose for my questions is accessing these technologies from
> applications. I develop both applications and databases. Working with
> Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test
> Environments.
Holy Cow! I read the other answers and just can't decide which one to
respond to... learned a lot reading them though.
Here's my take, never mind my footer, my answer is DBMS neutral.


> What is the purpose of a view if I can just copy the vode from a view
> and put it into a stored procedure?
The purpose of view is that it can be used within a query.
The optimizer of the DBMS can see through a view definition.
That means you can encapuslate complexity within a view while maximizing
lattitude for the optimizer.
Views are used for access control as well as to provide a level of
abstraction from the underlying DB Schema.

By contrast a procedure is a server side extension of your client
application. It's purpose can be three fold:
* Access control
* reduction in client server traffic
* concentrating processing cost on the server (thin client).

> Should I be accessing views from stored procedures?
They are orthogonal. Stored procedures do procedural logic views do
realtional transformations. So: Yes, absolutely!

> Should I use views to get information? and Stored Procedures for
> Inserts, Updates and Deletes?
No. You can INSERT, UPDATE and DELETE through views just fine.
Use stored procedures to encapsulate LOGIC.
USe views to encapsulate set processing (like JOINS, UNION, ...)

> What are the performance differences between the two?
There is little the DBMS can do to tune and parallelize a stored
procedure. Things happen exactly the way you code them.
There is a lot the optimizer can do with complex SQL including choosing
join orders and join types, exploiting SMP parallelism, ...

> Thank you for any and all information.
No problem.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Back to original message]