Microsoft – SQLServer – Views – Inline Macros
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:
- 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.
- 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.
- Talks about the NoExpand Query Hint to force “Indexed Views” usage
1) VIEWS – THEY OFFER NO OPTIMISATION BENEFITS; THEY ARE SIMPLY INLINE MACROS – USE SPARINGLY
2) Improving Performance with SQL Server v2005 Views
3) Insert or Update Pattern for SQL Server