|
|
Posted by Tony Rogerson on 10/01/25 11:38
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@yahoo.com> wrote in message
news:1138371543.540796.257960@o13g2000cwo.googlegroups.com...
> 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
>
Navigation:
[Reply to this message]
|