Francisco H Tapia
my.lists at verizon.net
Tue Jun 29 19:16:01 CDT 2004
me too, but all he wants is for you and me to go out and look for our own examples that we cannot re-code as a sproc. Arthur Fuller wrote On 6/29/2004 5:08 PM: >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 > > > > -- -Francisco