[dba-SQLServer] Views

Not to start a holy war, but I don't see any issues with Views.

Just like any tool in your toolbox, a view can be used efficiently and as
inefficiently as a cursor.  So why do I state this?... a view is simply a
result set defined for user display (or application display).  They carry a
great advantage when a sproc is not necessary.  Short lists are generally
the typical behavior for most applications such as a web app with multiple
drop down lists... in that situation you may/may not wish to use a view it
depends on what data is needed... ie, if the contents of the drop down are
dynamic, then a sproc is in order... however what if the results are static?
then there is no need to build up a sproc, as both result in a quick result
no matter what.

In my environment there have been instances where a view was justified... it
was designed to have a minimal amout of columns and the where clause was
designed to be a slice of time and data, the problem with a view of this
nature is that at some point the business will want to verify all the data
and will need a different (VIEW) that has all the resultsets... depending on
how you make the call from your client, you can just say select * from view
where columna = criteria.


additionally views have an added advantage that they can become indexed
views and therefor be optimized.


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


On Tue, Oct 7, 2008 at 8:08 AM, Arthur Fuller <fuller.artful at gmail.com>wrote:

> Views haven't been replaced. They still exist, but it is important to
> understand what they are. Beneath the covers, views are simply macros with
> names. (In Access lingo, they are named queries.) They are not compiled or
> optimized in the same way as stored procedures or table udfs. So depending
> on your perspective, they are either smart or stupid.
>
> For example, when I was doing a lot of Access ADP development, I found
> views
> to be an excellent approach for master-detail forms because Access would
> automatically do the filtering of the detail part. But the problem with
> views is that they don't accept parameters, and in large databases this is
> a
> killer. The alternatives are stored procedures and/or table udfs, both of
> which accept parameters.
>
> With a view you get back the whole result set and then your FE has to
> filter
> it. With a sproc or table udf, you supply parameters and get back only the
> relevant rows. If you are dealing with several million rows of which only
> 100 are of interest, this difference is dramatic.
>
> In my current app, I have one table that is currently at 35M rows and it
> will be 50M next month, and is expected to grow at that rate. A view on
> such
> a table (not to mention its JOINs) is preposterous. You never want to see
> all the data, you only want a narrow slice. Imagine a scenario in which 20
> users on a LAN want to look at some slice of this data. 35M rows get sent
> to
> each user, then filtered to the narrow slice on the client side. Using a
> sproc or table udf instead, and supplying parameters that define the slice,
> only the result set gets transmitted to the user. Add more users and the
> resulting time difference becomes increasingly dramatic.
>
> hth,
> Arthur
>
> On Mon, Oct 6, 2008 at 8:21 PM, Susan Harkins <ssharkins at gmail.com> wrote:
>
> > LOL -- how ambiguous of me. Sorry! ;)
> >
> > Yes, version 2008.
> >
> > I don't have 2008, but I haven't read or heard about views being replaced
> > by
> > something better, although, I can't imagine how -- they are such a nifty,
> > basic tool.
> >
> > Susan H.
> >
> >
> >
> _______________________________________________
> dba-SQLServer mailing list
> dba-SQLServer at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
> http://www.databaseadvisors.com
>
>