[dba-SQLServer] Views

On the other hand, many people will use a view to substitute for the table(s) against which the sproc or table udf is run. Nancy Lytle N_Lytle at terpalum.umd.edu



 EMAILING FOR THE GREATER GOODJoin me

> Date: Tue, 7 Oct 2008 12:08:41 -0300> From: fuller.artful at gmail.com> To: dba-sqlserver at databaseadvisors.com> Subject: Re: [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.> >> >> >> _______________________________________________> dba-SQLServer mailing list> dba-SQLServer at databaseadvisors.com> http://databaseadvisors.com/mailman/listinfo/dba-sqlserver> http://www.databaseadvisors.com>