Re: Views vs Stored Procedures, whats the difference?

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
.

Relevant Pages

  • Re: Views vs Stored Procedures, whats the difference?
    ... I develop both applications and databases. ... Working with Microsoft C#.NET and Microsoft SQL Server 2000 Production and 2005 Test Environments. ... The difference between a view and a stored procedure is exactly what the names say. ... you can define a select-query that retrieves specific information from one or more tables (a stored select-statement). ...
    (comp.databases.ms-sqlserver)
  • ADO works ADO.NET does
    ... I have two applications, one is a VB website using ... Server, with the same user id and password. ... receives a SQL Server does not exist error message. ... just call one stored procedure. ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: ADO works ADO.NET does
    ... Try SQL profiler to check the user name is the one you expect... ... > Server, with the same user id and password. ... just call one stored procedure. ... Even if I bring down the applications ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Access Control Best Practices for shared hosting seem at odds with Web Site Starters
    ... the full context of the bullet I quoted appears to be the HTTP POST ... Do not allow anonymous user to have write permission. ... content to the server, then they can write anything onto the server. ... More secure alternatives are applications that store ...
    (microsoft.public.inetserver.iis.security)
  • Re: SQL/ADO (2.8) Timeout Error - Cant resolve
    ... > Server 2000 back end. ... > timeout limit. ... And I’ve used SQL Query Analyzer to run the ... I am running a stored procedure that will backup a SQL Server ...
    (microsoft.public.sqlserver.clients)