使用 SQL 视图的充分理由是什么? [英] What is a good reason to use SQL views?

问题描述

我正在阅读 SQL Server 2008 圣经，并且正在介绍视图部分.但是作者确实没有解释视图的目的.视图有什么用?我应该在我的网站中使用它们吗?它们有什么好处?

I am reading through the SQL Server 2008 bible and I am covering the views section. But the author really doesn't explain the purpose of views. What is a good use for views? Should I use them in my website and what are the benefits of them?

推荐答案

前面的答案似乎都没有提到的另一个用途是更容易部署表结构更改.

Another use that none of the previous answers seem to have mentioned is easier deployment of table structure changes.

假设，您希望停用包含活跃用户数据的表 (T_OLD)，而是使用具有相似数据的新表(名为 T_NEW)，但有活跃用户和非活跃用户的数据，还有一列 active.

Say, you wish to retire a table (T_OLD) containing data for active users, and instead use a new table with similar data (named T_NEW) but one that has data for both active and inactive users, with one extra column active.

如果您的系统有无数查询执行SELECT what FROM T_OLD WHERE what，那么您有两种选择:

If your system(s) have gazillion queries that do SELECT whatever FROM T_OLD WHERE whatever, you have two choices for the roll-out:

1) Cold Turkey - 更改数据库，同时更改、测试和发布包含所述查询的大量代码.很难做到(甚至协调)，风险很大.不好.

1) Cold Turkey - Change the DB, and at the same time, change, test and release numerous pieces of code which contained said query. VERY hard to do (or even coordinate), very risky. Bad.

2) Gradual - 通过创建 T_NEW 表来更改数据库，删除 T_OLD 表并创建一个 VIEW 调用了 100% 模仿 T_OLD 表的 T_OLD(例如，视图查询是 SELECT all_fields_except_active FROM T_NEW WHERE active=1).

2) Gradual - change the DB by creating the T_NEW table, dropping the T_OLD table and instead creating a VIEW called T_OLD that mimics the T_OLD table 100% (e.g the view query is SELECT all_fields_except_active FROM T_NEW WHERE active=1).

这将允许您避免发布当前从 T_OLD 中选择的任何代码，并进行更改以将代码从 T_OLD 迁移到 T_NEW闲暇时.

That would allow you to avoid releasing ANY code that currently selects from T_OLD, and do the changes to migrate code from T_OLD to T_NEW at leisure.

这是一个简单的例子，还有很多其他的.

This is a simple example, there are others a lot more involved.

附言另一方面，您可能应该使用存储过程 API 而不是来自 T_OLD 的直接查询，但情况并非总是如此.

P.S. On the other hand, you probably should have had a stored procedure API instead of direct queries from T_OLD, but that's not always the case.

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