473,700 Members | 2,750 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

Jan 27 '06 #1
28 72507
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+vs+stored+procedure

Stevel
Jan 27 '06 #2
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***@yahoo.co m> wrote in message
news:11******** **************@ o13g2000cwo.goo glegroups.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

Jan 27 '06 #3
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/micro...cc4092b8603807

--
Hugo Kornelis, SQL Server MVP
Jan 27 '06 #4
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.

Jan 30 '06 #5
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
Jan 30 '06 #6
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
responsibilitie s.

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

Jan 30 '06 #7
>> Personally, I hate views. I can't control what the thing does, and sometimes the optimizer doesn't do what is best. <<

How does the smart money bet?
With a stored procedure, I have more options. With the more options, I do have more responsibilitie s. <<

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.
For sure I would agree to learn views first. It teaches data set mentality. <<
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
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.<<
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.
Stored procedures can be used to create data sets, and are MUCH more flexible in their capabilities then views. <<


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

Feb 4 '06 #8
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--" <jc*******@eart hlink.net> wrote in message
news:11******** *************@z 14g2000cwz.goog legroups.com...
Personally, I hate views. I can't control what the thing does, and
sometimes the optimizer doesn't do what is best. <<
How does the smart money bet?
With a stored procedure, I have more options. With the more options, I
do have more responsibilitie s. <<

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.
For sure I would agree to learn views first. It teaches data set
mentality. <<
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
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.<<
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.
Stored procedures can be used to create data sets, and are MUCH more
flexible in their capabilities then views. <<


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

Feb 4 '06 #9
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 stored
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 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. 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
Feb 4 '06 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
4538
by: David Nettles | last post by:
Two questions: (1) Does MySQL support VIEWS? (2) Does MySQL support STORED PROCEDURES? -- David Nettles web: http://www.miteyo.org email: tetsuoni3000@yahoo.co.jp
0
1983
by: SQLServer007 | last post by:
25 more days until the "get it free" promotion runs out for xSQL Object (you can get it from http://www.x-sql.com) Here are just some of the great features packed in the product: - Compare SQL Server objects (databases, tables, views, stored procedures, user defined data functions etc.) accross servers. - view and print dependencies; - generate color coded scripts for any object in the database or many of them at once (many configurable...
3
2481
by: Ryan.Chowdhury | last post by:
This is a general question regarding the use of view and stored procedures. I'm fairly new to databases and SQL. I've created a SQL database using an Access Data Project ("ADP") and I'm satified with the table structure. I've moved on to building some front ends for our users. I'm running into situations where I want subreports to be built from queries that are dependent on the values in other controls. I've played with stored...
45
3398
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
3
4383
by: codefragment | last post by:
Hi I have a chunky bit of sql that I will want to call from a number of places. It will return a few thousand rows. Whats the best way of structuring this? 1) I initially thought of using nested stored procedures and returning the result in a temporary table. However the scope of the temporary table seems to be limited to the stored procedure its created in so unless I create the temporary table in every stored
0
8731
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8649
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9220
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9082
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
8933
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7821
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
5904
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
1
3098
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
3
2032
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.