[dba-SQLServer] Views

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.
>
>
>