[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