Re: Views vs Stored Procedures, whats the difference?

Hi,

Think of a view as a 'virtual table', its not physical but is materialised
when you need it.

You can permission on the view, but users can write their own queries if
they are given access to the database which is often not recommended - its
the old Joe Bloggs running a select on the view in MS Access without a WHERE
clause!

If you are writing applications a better and more modular and secure
approach is to use stored procedures.

Encapsulate your logic into the stored procedure and call that from your
application, make suire you don't use dynamic sql in the app, instead use
the command object, basically don't do this in your app because you open
yourself up to sql injection...

dim strSQL as string

strSQL = "exec myproc @parm1='" & tbName.text & "'"

dbconn.Execute( strSQL )

>From a performance point of view, plans are kept now anyway so the old
addage that procs are better because of the execution plan being in cache is
no longer valid, because the execution plan from the view will be in cache
as well, probably parameterised as well.

In summary, if you want to build a good, secure modular system then use
stored procedures - one last thing, its significantly and i'm talking
significantly easier to performance tune a stored procedure, i often go on
site and sometimes find places with an app that has not used stored
procedures - its like tying my hands behind my back, i can only play with
indexes or index views; whereas a stored procedure i can rewrite the SQL
more efficiently.

Hope that helps.

Tony.

--
Tony Rogerson
SQL Server MVP
http://sqlserverfaq.com - free video tutorials


"mooreit" <mm_jg@xxxxxxxxx> wrote in message
news:1138371543.540796.257960@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
> 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.
>
> What is the purpose of a view if I can just copy the vode from a view
> and put it into a stored procedure?
>
> Should I be accessing views from stored procedures?
>
> Should I use views to get information? and Stored Procedures for
> Inserts, Updates and Deletes?
>
> What are the performance differences between the two?
>
> Thank you for any and all information.
>
> SBProgrammer
>


.

Relevant Pages

  • Re: SQL Access security from Window Application
    ... you could have your app handle authentication by emulating the ... Currently my application access the SQL using ... >System.Data.SqlClient and all forms use stored procedures. ... >passwords in a protected file but this creates a problem on remote systems ...
    (microsoft.public.dotnet.general)
  • How do I do Paging through a large dataset via Stored Procedures
    ... Paging by dynamically altering the SQL Query ... Create stored procedures ... SELECT * FROM STUDENTS ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: sql query or stored procudre which one is giving best performance in asp.net
    ... Stored procedures are better for performance. ... When you create one, SQL ... When you execute a SQL query ... direct the database needs to create the execution plan each time. ...
    (microsoft.public.dotnet.framework.aspnet)
  • Re: Transaction Oriented Architecture (TOA)
    ... appear to advocate wrapping SQL in OO constructs, ... writing code against the database. ... or perhaps stored procedures as a consolation. ... case or per app. ...
    (comp.object)
  • Re: Help with Stored Procedure
    ... I did mean stuff like system stored procedures (even ... build the query, compile it, and optimize it, then, then this is less ... very not easy using dynamic sql. ...
    (microsoft.public.sqlserver.programming)