Posted by Tony Rogerson on 02/04/06 18:31

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...
>>> 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
>

[Back to original message]