I’m just trying to get a general idea of what views are used for in RDBMSes. That is to say, I know what a view is and how to make one. I also know what I’ve used them for in the past.
But I want to make sure I have a thorough understanding of what a view is useful for and what a view shouldn’t be useful for. More specifically:
- What is a view useful for?
- Are there any situations in which it is tempting to use a view when you shouldn’t use one?
- Why would you use a view in lieu of something like a table-valued function or vice versa?
- Are there any circumstances that a view might be useful that aren’t apparent at first glance?
(And for the record, some of these questions are intentionally naive. This is partly a concept check.)
Advertisement
Answer
1) What is a view useful for?
IOPO In One Place Only
•Whether you consider the data itself or the queries that reference the joined tables, utilizing a view avoids unnecessary redundancy.
•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 good practice1 to offer only abstracted access to your underlying data (using views & table-valued functions), including views such asCREATE VIEW AS
SELECT * FROM tblData
1I hafta admit there’s a good deal of “Do as I say; not as I do” in that advice 😉
2) Are there any situations in which it is tempting to use a view when you shouldn’t use one?
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.)
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.
• See the derived table description in http://msdn.microsoft.com/en-us/library/ms177634.aspx
3) Why would you use a view in lieu of something like a table-valued function or vice versa?
(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.
4) Are there any circumstances that a view might be useful that aren’t apparent at first glance?
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 😉