Views vs Stored Procedures, whats the difference?

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

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.
>
> 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
>
The difference between a view and a stored procedure is exactly what the
names say. With a view, you can define a select-query that retrieves
specific information from one or more tables (a stored
select-statement). A stored procedure can do much more: it can run
multiple sql-commands, has control-of-flow statements and accepts
parameters. Also, giyf:
http://www.google.be/search? q=view...ro
cedure

Stevel

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
>

On 27 Jan 2006 06:19:03 -0800, 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.
>
>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

Hi SBProgrammer,

I wrote about the differences between stored procedures and views ten
days ago. Here's a link to the article on Google (warning - long URL,
might wrap)

http://groups.google.com/group/micr...b86038
07

--
Hugo Kornelis, SQL Server MVP

create a view.

then create that same view "into" a stored procedure.

everything you can do in a view you can do in a stored procedure. But
in a stored procedure, you can do MUCH more, much faster, and with much
more flexibility.

And then, you can do a whole lot more then that with a stored
procedure.  Think of a view as memorizing key strokes for an old time
macro.

Think of a stored procedure as a full fledged programming language.

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

hmmmm.......

Like Serge said, there are a lot of different answers and it depends a
lot on how you think about things.

Personally, I hate views. I can't control what the thing does, and
sometimes the optimizer doesn't do what is best. With a stored
procedure, I have more options. With the more options, I do have more
responsibilities.

For sure I would agree to learn views first. It teaches data set
mentality.  When views don't cut it, upgrade to stored procedures.
Shrug. that's just how I think about it.

A couple of dissentions. Within the stored procedure will be the exact
same code as is in the view, if you do the basics. The optimizer can
optimize that SQL code just like it can in the view.
Stored procedures can be used to create data sets, and are MUCH more
flexible in their capabilities then views.

As an example, perhaps you want 5 columns, one data row long, that are
calculated ffrom 7 tables. You can absolutely use a view to set up the
joins.
However, if you utilize a stored procedure, you can utilize your
knowledge of how the data really works to "optimize" your approach to
data retrieval.   So instead of a giant join, you can select the data
utilizing optimized indexes to rifle in on the data you are after.

For a small database (sub 10 million rows) it probably really doesn't
matter. Get a larger database, or a LOT of hits, and all of a sudden a
few extra table scans can really add up.

Shrug, Smile, and listening!!!!!!

-doug

>> Personally, I hate views. I can't control what the thing does, and someti
mes the optimizer doesn't do what is best. <<

How does the smart money bet?
 
responsibilities. <<

Myself, i do not want to have to control 100+ factors that can change
the next time I use  the procedure.  But T-SQL is a simple one-pass
compiler -- it does nto not re-arrange my if-the-else logic or optimize
my loops.
 

Yes.  And that is why a newbie likes it better than a VIEW.  Suddenly,
you have to change your mindset, how to use DCL and WITH CHECK OPTIONs
 

Not really.  Procedure programmer will tend to use if-then-else while a
VIEW programmer would tend to use CASE expressions.  Procedure
programmer will tend to use temp tables as scratch files while a VIEW
programmer would tend to use CTE and derived tables in the query.
 

Only because they can take parameters.  The two things serve different
purposes

If you use VIEWS to encapsulate logic and your security just how do you
prevent a user from connecting to the database and writing their own
queries?

Answer: With VIEWS you can't (easily) but with stored procedures they don't
get the opporunity, nor is your schema design (and possible pitfalls) sent
across the wire for all and sundry to see your bad habits - and exploit
them!

You view will have a different plan each time its compiled anyway - when
values change, statistics distribution etc... it behaves just like a stored
procedure in that respect.

The smart money use stored procedures because:

1) they neatly encapsulate logic so
a)    it can be easily developed in a multi-person team/teams
b)    faults can be very easily diagnosed and solved without having
to revert to a nasty application recompile and redistribution
2) they are really great for implementing proper security, your schema
is not exposed to anybody except administrators and app devs.
3) you can code IF ELSE to make more efficient queries instead of
getting general plans and tons of code that needs testing and supporting

Tony Rogerson, SQL Server MVP
http://sqlserverfaq.com



"--CELKO--" <jcelko212@earthlink.net> wrote in message
news:1139018775.865055.15560@z14g2000cwz.googlegroups.com... 
>
> How does the smart money bet?
> 
> responsibilities. <<
>
> Myself, i do not want to have to control 100+ factors that can change
> the next time I use  the procedure.  But T-SQL is a simple one-pass
> compiler -- it does nto not re-arrange my if-the-else logic or optimize
> my loops.
> 
>
> Yes.  And that is why a newbie likes it better than a VIEW.  Suddenly,
> you have to change your mindset, how to use DCL and WITH CHECK OPTIONs
> 
>
> Not really.  Procedure programmer will tend to use if-then-else while a
> VIEW programmer would tend to use CASE expressions.  Procedure
> programmer will tend to use temp tables as scratch files while a VIEW
> programmer would tend to use CTE and derived tables in the query.
> 
>
> Only because they can take parameters.  The two things serve different
> purposes
>

Mime-Version: 1.0
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 7bit
X-Trace: individual.net  ht6ZINJsWCE3NMJUHZJw
zwJzMvlnHCo4n1/8Qtq3ge5KuWlHo7
User-Agent: Mozilla Thunderbird 1.0.2 (Windows/20050317)
X-Accept-Language: en-us, en
In-Reply-To: < ds2ksa$f58$1$8300dec
7@news.demon.co.uk>
Xref: number1.nntp.dca.giganews.com comp.databases.ms-sqlserver:161700

Tony Rogerson  wrote:
> If you use VIEWS to encapsulate logic and your security just how do you
> prevent a user from connecting to the database and writing their own
> queries?
>
> Answer: With VIEWS you can't (easily) but with stored procedures they don'
t
> get the opporunity, nor is your schema design (and possible pitfalls) sent
> across the wire for all and sundry to see your bad habits - and exploit
> them!
One does not exclude the other. You can use procedures as external
interface for the application and still use views within the procedures
as appropriate.
>
> You view will have a different plan each time its compiled anyway - when
> values change, statistics distribution etc... it behaves just like a store
d
> procedure in that respect.
Absolutely not true.
A stored procedure logic will be exactly as fast as the algorithm you
chose when you wrote it. The DBMS can neither optimize nor parallelize
stored procedure logic. It can only optimize teh small pieces of SQL
that you left in.
The SQL inside the view will be as fast as the optimizer can make it
depending on the statistics using 30 years of research. The entire idea
RDBMS and SQL is to separate the WHAT from the HOW. Anytime you use
logic you take the HOW away from the RDBMS and you have exactly one
choice to combine results: Nested loop join (aka nested cursors).

> The smart money use stored procedures because:
The fast money perhaps, absolutely not the smart money.
Folks like Joe and I spend a lot of time digging companies who fell prey
to this thinking out the ditch.

>     1) they neatly encapsulate logic so
>         a)    it can be easily developed in a multi-person team/teams
>         b)    faults can be very easily diagnosed and solved without havin
g
> to revert to a nasty application recompile and redistribution
>     2) they are really great for implementing proper security, your schema
> is not exposed to anybody except administrators and app devs.
Both points above are orthoginal to the usage of views.
>     3) you can code IF ELSE to make more efficient queries instead of
> getting general plans and tons of code that needs testing and supporting
How good is your QA, do you design limits testcases for each and every
combination of IF THEN ELSE logic? Do you believe the harm of a nested
cursor is undone by the advantage of an IF THEN ELSE statement?
I understant that SQL Server supports hints.
Try a couple experiements forcing SQL Server to use different join
implementations (nestedloop, merge, hash, ..) on decent sized tables,
then think about whether you can afford nested loop (i.e. nested cursors).
My company sells hardware, I don't mind if the stuff you produce
requires resources beyond measure :-)

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