StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p><i>1) What is a view useful for? </i></p> <blockquote><b>IOPO</b> In One Place Only<br/><br/>•Whether you consider the data itself or the queries that reference the joined tables, utilizing a view avoids unnecessary redundancy. <br/><br/>•Views also provide an abstracting layer preventing direct access to the tables (and the resulting handcuffing referencing physical dependencies). In fact, I think it's <i>good practice</i><sup>1</sup> to offer only abstracted access to your underlying data (using views & table-valued functions), including views such as <br/><br/><code>CREATE VIEW AS <br/>      SELECT * FROM tblData</code><br/><br/><sup>1</sup>I hafta admit there's a good deal of "Do as I say; not as I do" in that advice ;)</blockquote> <p><i>2) Are there any situations in which it is tempting to use a view when you shouldn't use one? </i></p> <blockquote>Performance in view joins used to be a concern (e.g. SQL 2000). I'm no expert, but I haven't worried about it in a while. (Nor can I think of where I'm presently using view joins.)<br/><br/>Another situation where a view might be overkill is when the view is only referenced from one calling location and a derived table could be used instead. Just like an anonymous type is preferable to a class in .NET if the anonymous type is only used/referenced once.<br/><br>    • See the derived table description in   http://msdn.microsoft.com/en-us/library/ms177634.aspx</blockquote> <p><i>3) Why would you use a view in lieu of something like a table-valued function or vice versa? </i></p> <blockquote>(Aside from performance reasons) A table-valued function is functionally equivalent to a parameterized view. In fact, a common simple table-valued function use case is simply to add a WHERE clause filter to an already existing view in a single object.</blockquote> <p><i>4) Are there any circumstances that a view might be useful that aren't apparent at first glance?</i> </p> <blockquote>I can't think of any non-apparent uses of the top of my head. (I suppose if I could, that would make them apparent ;)</blockquote>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload