[dba-SQLServer] Difference between views and queries

Billy Pang tuxedo_man at hotmail.com
Wed Jun 30 00:01:28 CDT 2004


sorry... just reread what I wrote... instead of:

"it is not possible for a sproc to query table in another db that the sproc 
does not reside in."

rather, it should have read....

"it is difficult for a sproc to query other tables in other databases after 
the sproc has been created."

Billy


>From: "Billy Pang" <tuxedo_man at hotmail.com>
>Reply-To: dba-sqlserver at databaseadvisors.com
>To: dba-sqlserver at databaseadvisors.com
>Subject: RE: [dba-SQLServer] Difference between views and queries
>Date: Wed, 30 Jun 2004 04:56:50 +0000
>
>oh boy, can't wait to get into this discussion :)...there is one example I 
>can think of..  the example would be based on the physical storage of the 
>sproc in a single db.  it is not possible for a sproc to query table in 
>another db that the sproc does not reside in.
>
>To illustrate, let's say that there are three copies of the northwind db on 
>db server: northwind0, northwind1 and northwind2; all three have exact db 
>schema;
>
>Your goal is to develop a report that counts number of records in the 
>products table in each of the northwind databases.  sometimes you may only 
>want to count records in northwind0 and northwind1 but not northwind2; 
>sometimes you may only want northwind2 and northwind1 but not northwind0; 
>and sometimes you want all three northwind dbs in your count.  It is 
>possible to create a sp for each of the permutation but if a fourth 
>northwind db is introduced to the system, then the code base is doubled.
>
>The alternate solution would be to build dynamic sql to piece together 
>select statement referencing tables using the [db].[owner].[table] format; 
>which tables are pieced together is based on what the user selects as 
>criteria for report.  Not elegant solution but it is a lot less code to 
>maintain.
>
>With that being said, dynamic sql is not as safe for reporting compared to 
>sproc.
>
>I think what Francis is trying to get at is that it is not possible for a 
>developer to claim that a particular "coding method" is never needed.  
>Rather, it is more correct to state that it is not likely that a particular 
>"coding method" is ever needed.  Difference between the words "impossible" 
>and "improbable".  Similiar to how a judge cannot hand out a verdict to 
>court case before the case is presented no matter obvious it may seem.
>
>Another point of reference on this topic would be look up "Validating User 
>Input" in BOL for best practices on this issue.
>
>Billy
>
>
>>From: "Arthur Fuller" <artful at rogers.com>
>>Reply-To: dba-sqlserver at databaseadvisors.com
>>To: <dba-sqlserver at databaseadvisors.com>
>>Subject: RE: [dba-SQLServer] Difference between views and queries
>>Date: Tue, 29 Jun 2004 20:08:54 -0400
>>
>>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
>>
>
>_________________________________________________________________
>MSN Premium with Virus Guard and Firewall* from McAfee® Security : 2 months 
>FREE*   
>http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines
>
>_______________________________________________
>dba-SQLServer mailing list
>dba-SQLServer at databaseadvisors.com
>http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
>http://www.databaseadvisors.com
>

_________________________________________________________________
http://join.msn.com/?pgmarket=en-ca&page=byoa/prem&xAPID=1994&DI=1034&SU=http://hotmail.com/enca&HL=Market_MSNIS_Taglines




More information about the dba-SQLServer mailing list