视图有什么用? [英] What are views good for?

问题描述

我只是想大致了解 RDBMS 中使用的视图.也就是说，我知道什么是视图以及如何创建视图.我也知道我过去用它们做什么.

但我想确保我对视图的用途和视图不应该有用的内容有透彻的了解.更具体地说:

1. 视图有什么用?
2. 在哪些情况下您不应该使用视图却很容易使用视图?
3. 为什么要使用视图来代替表值函数之类的东西，反之亦然?
4. 是否有任何乍一看并不明显的视图可能有用的情况?

(为了记录，其中一些问题是故意幼稚的.这部分是概念检查.)

解决方案

1) 视图有什么用?

IOPO 仅在一处

•无论您考虑数据本身还是引用连接表的查询，使用视图都可以避免不必要的冗余.

•视图还提供了一个抽象层，防止直接访问表(以及由此产生的引用物理依赖项的手铐).事实上，我认为 良好做法¹ 仅提供对基础数据的抽象访问(使用视图和表值函数)，包括诸如
之类的视图
创建视图为
   SELECT * FROM tblData

¹我不得不承认在那个建议中有很多按照我说的做；不要按照我做的";)

2) 是否有任何情况下您不应该使用视图时很想使用视图?

视图连接中的性能曾经是一个问题(例如 SQL 2000).我不是专家，但我已经有一段时间没有担心了.(我也想不出我目前在哪里使用视图连接.)

另一种视图可能过度使用的情况是，视图仅从一个调用位置引用并且可以使用派生表反而.就像匿名类型比 .NET 中的类更可取，如果匿名类型只使用/引用一次.

  &公牛;请参阅   中的派生表说明http://msdn.microsoft.com/en-us/library/ms177634.aspx

3) 为什么要使用视图来代替表值函数之类的东西，反之亦然?

(除了性能原因)表值函数在功能上等同于参数化视图.事实上，一个常见的简单表值函数用例只是向单个对象中的现有视图添加 WHERE 子句过滤器.

4) 是否有任何乍一看并不明显的视图可能有用的情况?

我想不出头顶有任何不明显的用途.(我想如果可以的话，那会让它们变得明显；)

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:

1. What is a view useful for?
2. Are there any situations in which it is tempting to use a view when you shouldn't use one?
3. Why would you use a view in lieu of something like a table-valued function or vice versa?
4. 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.)

解决方案

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 practice¹ to offer only abstracted access to your underlying data (using views & table-valued functions), including views such as

CREATE VIEW AS
      SELECT * FROM tblData

¹I 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 ;)

这篇关于视图有什么用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！