Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

On Indexes and Views Expand / Collapse
Author
Message
Posted Tuesday, September 9, 2008 12:45 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:29 PM
Points: 752, Visits: 920
Comments posted to this topic are about the item On Indexes and Views

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #565930
Posted Tuesday, September 9, 2008 3:54 AM
SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Today @ 1:47 AM
Points: 5,448, Visits: 1,401
Nice one.


Post #565999
Posted Tuesday, September 9, 2008 5:44 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Wednesday, January 2, 2013 12:15 PM
Points: 1,443, Visits: 711
Excellent article! Indexed views are a useful tool to solve performance problems when properly implemented.
Post #566052
Posted Tuesday, September 9, 2008 5:50 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

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
Post #566059
Posted Tuesday, September 9, 2008 6:00 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 35,593, Visits: 32,183
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."

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #566065
Posted Tuesday, September 9, 2008 6:01 AM


SSC-Dedicated

SSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-DedicatedSSC-Dedicated

Group: General Forum Members
Last Login: Today @ 4:28 PM
Points: 35,593, Visits: 32,183
Timothy! Great article on NO EXPAND. Nicely done!

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

(play on words) "Just because you CAN do something in T-SQL, doesn't mean you SHOULDN'T." --22 Aug 2013

Helpful Links:
How to post code problems
How to post performance problems
Post #566066
Posted Tuesday, September 9, 2008 8:08 AM


SSC Eights!

SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!SSC Eights!

Group: General Forum Members
Last Login: Friday, February 4, 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
Post #566180
Posted Tuesday, September 9, 2008 9:22 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, November 1, 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.
Post #566260
Posted Tuesday, September 9, 2008 9:32 AM


Right there with Babe

Right there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with BabeRight there with Babe

Group: General Forum Members
Last Login: 2 days ago @ 4:29 PM
Points: 752, Visits: 920
Adrian, that is a great idea on the nomenclature. Thanks for pointing it out.

---
Timothy A Wiseman
SQL Blog: http://timothyawiseman.wordpress.com/
Post #566272
Posted Tuesday, September 9, 2008 10:14 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: 2 days ago @ 2:38 PM
Points: 1,386, Visits: 825
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
Post #566304
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse