|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: 2 days ago @ 12:40 PM
Points: 748,
Visits: 905
|
|
|
|
|
|
SSCarpal Tunnel
       
Group: General Forum Members
Last Login: Tuesday, June 11, 2013 5:01 AM
Points: 4,815,
Visits: 1,343
|
|
|
|
|
|
UDP Broadcaster
      
Group: General Forum Members
Last Login: Wednesday, January 02, 2013 12:15 PM
Points: 1,443,
Visits: 711
|
|
| Excellent article! Indexed views are a useful tool to solve performance problems when properly implemented.
|
|
|
|
|
Grasshopper
      
Group: General Forum Members
Last Login: Friday, April 20, 2012 6:54 AM
Points: 12,
Visits: 86
|
|
This is nice however, how can u make a view more efficient when you are using other views or a linked query in the view? I guess you can't create an index on these views so are there any tips out there on these types of views? :)
Thanks, Mike
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112,
Visits: 27,038
|
|
M Roush (9/9/2008) This is nice however, how can u make a view more efficient when you are using other views or a linked query in the view? I guess you can't create an index on these views so are there any tips out there on these types of views? :)
Thanks, Mike
Depending on what you filter a view-of-a-view on in a query, views of views can be horrible for performance because you will usually cause the inner view to materialize in full if you filter or join on a calculated column.
--Jeff Moden "RBAR is pronounced "ree-bar" and is a "Modenism" for "Row-By-Agonizing-Row".
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column."
For better, quicker answers on T-SQL questions, click on the following... http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following... http://www.sqlservercentral.com/articles/SQLServerCentral/66909/
|
|
|
|
|
SSC-Dedicated
           
Group: General Forum Members
Last Login: Today @ 7:18 AM
Points: 33,112,
Visits: 27,038
|
|
|
|
|
|
SSC Eights!
      
Group: General Forum Members
Last Login: Friday, February 04, 2011 7:20 AM
Points: 977,
Visits: 1,499
|
|
Timothy,
That's good information, and a great example.
Thanks!
One question anyone? I thought that Developer Edition was equivalent to Enterprise Edition, but mine acts like Standard Edition when I don't use NO EXPAND.
Anybody know why?
Tom Garth Vertical Solutions
"There are three kinds of men. The one that learns by reading. The few who learn by observation. The rest of them have to pee on the electric fence for themselves." -- Will Rogers
|
|
|
|
|
SSC-Enthusiastic
      
Group: General Forum Members
Last Login: Thursday, November 01, 2012 1:18 PM
Points: 110,
Visits: 261
|
|
I have been using the same approach for my sql2k5 standard boxes with great success. I have found that I routinely want to know if a view is (1) an indexed view, (2) a wrapper view for an indexed view, or (3) a traditional view. To that end, I use a nomenclature of *_BaseIV and *_IV for the first two cases-
create table t1 (c1 int primary key); go /*indexed view*/ create view myview1_BaseIV with schemabinding as select c1 from dbo.t1; go create unique clustered index myview1baseiv_ucidx_c1 on dbo.myview1_baseiv(c1); go
/*wrapper for indexed view*/ create view myview1_IV as select c1 from dbo.myview1_BaseIV with (noexpand);
Naming them this way makes it so I can easily discern what type of view each is when scanning through the list of views in SSMS. I'll often times be looking for an indexed view that is an aggregate rollup of some table, so this makes it quite easy to locate.
|
|
|
|
|
Right there with Babe
      
Group: General Forum Members
Last Login: 2 days ago @ 12:40 PM
Points: 748,
Visits: 905
|
|
|
|
|
|
Ten Centuries
      
Group: General Forum Members
Last Login: Tuesday, August 07, 2012 6:59 AM
Points: 1,386,
Visits: 823
|
|
a well-written article, and with appropriate citations, no less! Thanks Timothy. I'll be looking for more articles from you...
Cheers!
A lowly developer
|
|
|
|