[dba-SQLServer] Views

Arthur,
  I think there is a misunderstanding... see... if you send a queued command
to sql server as in dynamic sql, that reads...

Select * from mytable where pkid = 124....

then the sql engine will only provide the client with pkid=124, nothing
else, a view works in a similar fashion to a table and thus these two
statements may be synonymous

select * from myview where pkid = 124


The only time that all data is returned to a client if the statement omits
the where clause.  This is the way that sql server works.  The benefits of
using a stored procedure over a view is that stored procedures follow a
query plan that is usually optimized.  While the later versions of sql
server take advantage of cached data, it's possible that the query optimizer
will choose a different path for your view even though a previous plan may
have worked better for it.

so in short, to a client, a view and a stored procedure return data in much
the same way... the advantages of a stored procedure outweight  a view
because you can return  a single result, a table result or xml docs and only
the data you really need is returned based on the pre-programming of the
stored procedure.... views can have parameters within, and any additional
where clauses help to further filter down the results, but unfortuantely the
view results are table result based, thus you cannot return xml docs or just
simply a single value (unless it's a single column based resultset...

you also can't create while loops or (dare i say) cursors within the
views... so that also makes them limiting.

I hope this helps clarify what data makes it to the client.

btw, if the argument here is that you are linking a view directly to access
then yes, all the data is sent to access and access further fileters the
results by adding the where clause there, however if you link the view as a
pass-through query, you loose nothing in performance by pre-filtering the
results at the sql engine instead of at access.


-Francisco
http://sqlthis.blogspot.com | Tsql and More...


On Wed, Oct 8, 2008 at 10:15 AM, Arthur Fuller <fuller.artful at gmail.com>wrote:

> That is my understanding of the documentation, Susan. The view pulls
> everything to the client, which then filters out everything not
> interesting.
> I could be wrong about this but it would be relatively easy to generate a
> test. Unfortunately, today I have lots of work on my plate, but by tomorrow
> I will have a 50M row table on which to test this hypothesis.
>
> A.
>
> On Wed, Oct 8, 2008 at 2:03 PM, Susan Harkins <ssharkins at gmail.com> wrote:
>
> > > SELECT * FROM MyView WHERE PK = 124
> > >
> > > results in the case of a view that all rows in the view are sent to the
> > > client and then the client filters the result set.
> >
> > =======Arthur, I'm not sure I understand -- are you saying that if MyView
> > contains 100 records but only 2 that satisfy the WHERE clause, the view
> > still pulls all 100 records but only displays 2?
> >
> > Susan H.
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>