Microsoft – SQLServer – Views – Inline Macros

Microsoft – SQLServer – Views – Inline Macros

Tony Roggerson blogging @ http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/03/views-they-offer-no-optimisation-benefits-they-are-simply-inline-macros-use-sparingly.aspx

has a good and insightful entry about Views, Indexed Views, Table Value Functions (TVFS).

A couple of other DBAs (GrumpyOldDBA & Adam Mechanic) chimed in, as well.

Basically, the blog entry covers:

  1. At least in SQL Server Versions up to v2005, Views are basically inline macros.  For those who have programmed in C the term inline macros suffices.  For others, it simply means that the SQL behind a View is not compiled.    But, added \ pasted on to each reference.That fact that SQL does not create a separate compiled version makes a bit of sense since the Referencing Query might have broader data and opportunity per optimization.
  2. Indexed Views were berated for preventing in-place updates  –  They cause page-splits, lock contention & resultant deadlocks.As MS places strong pre-requisites and requirements around their usage, it is a bit difficult to “sneak” them in.
  3.  Talks about the NoExpand Query Hint to force “Indexed Views” usage

 

References:
1) VIEWS – THEY OFFER NO OPTIMISATION BENEFITS; THEY ARE SIMPLY INLINE MACROS – USE SPARINGLY

http://sqlblogcasts.com/blogs/tonyrogerson/archive/2008/01/03/views-they-offer-no-optimisation-benefits-they-are-simply-inline-macros-use-sparingly.aspx

2) Improving Performance with SQL Server v2005 Views
http://technet.microsoft.com/en-us/library/cc917715.aspx

3) Insert or Update Pattern for SQL Server
http://samsaffron.com/blog/archive/2007/04/04/14.aspx 

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s