Re: Views vs Stored Procedures, whats the difference?

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@xxxxxxxxxxxxx> wrote in message
news:1139018775.865055.15560@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
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
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.

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



.

Relevant Pages

  • Re: Is it time for secure C ?
    ... This is certainly possible (especially with C99 VLAs ... but is the proposed "security" worth ... array out with NUL bytes is removed! ... the programmer to provide output character arrays big enough to hold ...
    (comp.lang.c)
  • Re: security enhacement to C runtime library (XXX_s)
    ... In the below link MS announces a security update to the C runtime ... Every buffer overflow error that was made before can still be ... strings in C the way they are used in every other programming ... how can we increase the programmer ...
    (comp.std.c)
  • Re: pid from startet process
    ... programmer expects. ... generate new candidates for PIDs by incrementing a counter. ... have not, however, yielded on the claim of a security threat posed ... the introduction of a system with randomized PIDs increased ...
    (comp.lang.perl.misc)
  • Re: [Full-Disclosure] Antigen Path Disclosure
    ... Im not a part of the security industry itself, im a programmer. ... But Still decided to uploaded 2 dodgey files and a jpg like he was ...
    (Full-Disclosure)
  • Re: need free support in IT? [Glasgow]
    ... I am a Sun Certified Programmer for the Java 2 Platform, ... GNU/Linux operating system administration (security, samba, Apache, ... interested in Open Source operating systems and software, networking, ... If you think, that your company could take advantage of my skills, and, ...
    (uk.local.glasgow)