[dba-SQLServer] Difference between views and queries

Thats how we do it. We copy the live data out to another server and database
and the users report aganist that one. But then the data they use will only
change slightly over the academic year. I have never seen reporting tools
used on a live database anywhere I have worked. Thats not to say it dosnt
happen or is bad I just havnt seen it.

Martin

----- Original Message ----- 
From: "Francisco H Tapia" <my.lists at verizon.net>
To: <dba-sqlserver at databaseadvisors.com>
Sent: Thursday, June 10, 2004 9:38 PM
Subject: Re: [dba-SQLServer] Difference between views and queries


> SUCH developers need to be dragged out to the street to be tarred and
> feathered... ;o) j/k
> no...
>
> A lot of times I find that my boss will request that he wants a new
> table w/ a join to another table and somehow magically come up w/ some
> solution for some third criteria.  The fact remains that "HE" is not a
> developer.  that's why he hired me.  I know that sounds arrogant, but
> let's consider the performance of your SQL Server, if you have adequate
> RAM, CPU and a marginally good IO subsystem; well then your performance
> may even be acceptable.  I have no idea how long it takes to run "any"
> of your reports, but the ones we've written so far in Access whose
> recordsources are stored procedures, well, the result has been < 20
> seconds for reports that used to take well over 5 minutes in Access.  In
> fact just about all report run in the 2-3 seconds timespan which has
> helped make our new Access.ADP system shine.
>
> One thing that they've been missing is the ODBC access to tables to
> create their own reports. Many other SQL Developers often find a
> combination of VIEWS that are interpreted to the End Users as tables to
> help fill this gap, thus you'd have ODBC views which the users would
> think are "TABLES".  This will satisfy direct linking to tables and
> possibly promote better access time (tho not guarnateed, because as we
> all know, it is definatly easy enough to write a poorly joined SQL
> statement).
>
> another solution some developers use is to have another SERVER,
> generally known as a reporting server for processing CUBES of data.  In
> this scenario, data is replicated TO it by the LIVE system in generally
> a delayed Transactional method.  Many times the data is flattened out
> and de-normalized.  This helps hide ID's that are meaningless to
> endusers, and for them to have direct "table" access to such system.
> These systems often have to be guarded and monitored.
>
>
> so in your scenario the answer really is, "it depends" :).  If Views are
> a possiblity and your system is Not just for datawarehousing, then
> definitly I'd push in that direction.  If it were my system, I wouldn't
> want to be considered responsible for lack of availablity should people
> mis-construct their reports.
>
>
> Nicholson, Karen wrote On 6/10/2004 1:03 PM:
>
> >So what do you think of having tons of Crystal Reports that hit against
the
> >sql server (odbc) tables?  I prefer to create views for the Crystal
Reports
> >to read,
> >but everyone in this corporation likes to hit against the tables and
write
> >the
> >groupings, calculations, etc in Crystal.
> >
> >-----Original Message-----
> >From: dba-sqlserver-bounces at databaseadvisors.com
> >[mailto:dba-sqlserver-bounces at databaseadvisors.com]On Behalf Of
> >Francisco H Tapia
> >Sent: Thursday, June 10, 2004 3:58 PM
> >To: dba-sqlserver at databaseadvisors.com
> >Subject: Re: [dba-SQLServer] Difference between views and queries
> >
> >
> >jwcolby wrote On 6/10/2004 9:33 AM:
> >
> >
> >
> >>Can anyone explain the difference between a view and a query?  Views use
a
> >>query, plus the view keyword.  I have a couple of books that I have read
> >>
> >>
> >the
> >
> >
> >>chapter on Views, but I so far haven't managed to "get" why you wouldn't
> >>just use the query itself instead of turning it into a view.
> >>
> >>
> >>
> >>
> >A query is a request for an Access Database, however for Sql Server you
> >would either use a View or Stored Procedure to return the data you
> >wanted... you are also able to use dynamic SQL to retrieve the
> >information you need.  ANY request given to the SQL Server engine is
> >managed by the engine, unless you are running Remote servers (iirc).
> >
> >In Sql Server, it is TABOO, nay, GENERALLY bad practice to use dynamic
> >sql because of the implication of SQL INJECTION attacks, this poses a
> >"real" security threat to your database. and your server.
> >
> >another reason to use a VIEW over dynamic sql is that it is
> >pre-optimized by the SQL Server Engine and thus runs faster and more
> >efficient.  Additionally if you use Dynamic SQL then your individual
> >users who access the server will need EXPLICIT "SELECT" permissions by
> >you, which is another 'bad' practice.  In SQL Server you make data
> >available to your users via VIEWs and Stored Procedures or some other
> >secure way in order to protect your tables and it's data.
> >
> >ya get wot I mean?
> >
> >
> >
>
>
> -- 
> -Francisco
>
>
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>