[dba-SQLServer] Difference between views and queries

Hi Arthur:

I think, given a limited set of options, assuming you are in control of
writing the program, from top to bottom, there is no reason why, all the SQL
interface could not be managed with static SPs. ...but if the option range
becomes extremely complex then a more dynamic set will be required. I do not
think it is a matter of right or wrong but keeping the complexity of the
program down to a point where you, as the programmer, can still manage it.

I have a friend who had worked, with a team, on huge system, for some giant
oil conglomerate, out of Calgary and they had managed to badly bend SQL2000,
almost to it's knees, using a complex sets of dynamic function calls and
re-naming techniques, right within the SP. As I understand, the security was
a nightmare, but much of the internal SPs handled that by checking the roles
against the procedure caller. Mind you, the system ran better than it
predecessor, a full-blown Oracle DB, which they tossed after a year. On the
other hand, I think, for most practical purposes, the sky is the limit, if
you what to design all your processing within SQL SPs.

My two cents worth.
Jim

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of Arthur
Fuller
Sent: Tuesday, June 29, 2004 5:09 PM
To: dba-sqlserver at databaseadvisors.com
Subject: RE: [dba-SQLServer] Difference between views and queries


All I want is an example that cannot be coded by one or more sprocs.

Arthur

-----Original Message-----
From: dba-sqlserver-bounces at databaseadvisors.com
[mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf Of Francis
Harvey
Sent: Tuesday, June 29, 2004 10:47 AM
To: 'dba-sqlserver at databaseadvisors.com'
Subject: RE: [dba-SQLServer] Difference between views and queries


Francisco,

I would like to consider the opinion of someone who shares my namesake
as seriously as possible, but in this case, I believe you suffer from
Arthur's problem of a lack of experience with the very specific problems
I cited. It is fine to talk about not using dynamic SQL in general when
using SQL Server, but just as with normalization, there are exceptions
and no one should be stating it as an absolute rule or bad practice
until they have reviewed the problems I mentioned. Again, I will state,
for those particular problems, dynamic SQL is often the superior
solution.

Also, the "scaling" issue is teetering awfully close to using FUD. You
can always use any programming method incorrectly, but correctly written
dynamic SQL does not suffer from the horrible efficiency loss that not
having a cached query plan would seem to imply. Similarly, the "time
spent" argument is bad logic. I don't spend less time on my dynamic SQL
solutions, I spend more time because the problems are difficult. Adding
more time for a static SQL solution will not result in a better design
or even a working design in some cases.

I don't think you, Arthur or I are in disagreement over security, I just
didn't like the oversimplified way security was described, so I chose to
emphasize a particular point. I think this is an important part that
should be highlighted.

Francis R Harvey III
WB 303, (301)294-3952
harveyf1 at westat.com


> -----Original Message-----
> From: dba-sqlserver-bounces at databaseadvisors.com
> [mailto:dba-sqlserver-bounces at databaseadvisors.com] On Behalf
> Of Francisco H Tapia
> Sent: Monday, June 28, 2004 12:42 PM
> To: dba-sqlserver at databaseadvisors.com
> Subject: Re: [dba-SQLServer] Difference between views and queries
>
>
> Francis,
>    I respectfully agree w/ Arthur on this.  Dynamic SQL as a rule, is
> bad design.  Many times over you will find that there are comparable
> solutions that often meet the demand much better, and "scale" much
> better than that of dynamic sql.  There is no "ONE" right way
> of doing
> things, that is the nature of this business.  However
> justifying dynamic
> sql because of some time spent on design is imnsho incorrect.
>  You will
> often find more times that enabling users/roles via Views/Sprocs and
> Functions to be far superior solutions than to open wide dynamic sql.
>
> re: security.
> Of course if you deny permissions on any object the take
> precedence over
> allows, this is the nature of security.  That is why you must be
> explicitly "SURE" when making DENY requests on any object.
> NOT giving
> rights to an object does not equeal REJECT.
>
> If I do not give a user rights to a view, under Role1, but
> then give him
> rights to that object via Role2.  There will be 0 issues w/
> accessability.  However If by poor planning I had a "REJECT"
> rights in
> Role1, then I'd have the issues you are stating.
>
>
>
>
> Francis Harvey wrote On 6/25/2004 2:24 PM:
>
> >Arthur,
> >
> >Do some research on the basic solutions for SQL that has to
> adjust for
> >varying databases, conditions (dynamic search), or servers (different

> >SQL dialects). Some designs also call for encapsulating
> business logic
> >outside of the data tier. In these situations, dynamic SQL is often
> >the only logical choice. I have only had to deal with dynamic search
> >myself, but this is enough to convince me of its usefulness.
> >
> >Describing permissions solely as additive in nature is
> incorrect. This
> >is the point I was making.
> >
> >Francis R Harvey III
> >WB 303, (301)294-3952
> >harveyf1 at westat.com
<snip>
_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com

_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com