第十一周翻译：Stairway to T-SQL: Beyond The Basics Level 4: Using Views to Simplify Your Query

原文链接：http://www.sqlservercentral.com/articles/Stairway+Series/107120/

递接到T-SQL：在原有的基本4层之后：使用视图简化查询

系列：本文是递接系列的一部分：递接到T-SQL：基础之后

 从递增到T-SQLDML，GregoryLarsen介绍了T-SQL语言的更加高级的一个方面，比如子查询。

在这个层次上，我将讨论如何使用数据库视图来简化Transact-SQL(T-SQL)代码。通过了解如何使用视图，大家将能够更好地支持编写T-sql代码以满足复杂的业务需求。在本文中，我将讨论什么是数据库视图，然后提供一些示例来帮助理解如何使用视图来实现在不同的代码场景时。

 

什么是视图？

视图是由行和列组成的虚拟表。数据可以来自单个表，也可以来自多个表。查询视图就像查询普通表一样。创建视图时，创建视图语句并存储在创建该视图的数据库中。

 

以下是视图可以帮助实现编码逻辑的一些情况：

1.您不想向查询表的用户公开表的所有列。

2.您的数据库架构设计很复杂，因此您可以构建视图以简化用户访问。

3.您希望更改数据库架构设计，但希望保持兼容性，这样就不必重写现有代码。

更好地理解如何使用视图的最佳方法是通过一些使用视图来满足不同业务需求的示例。

 

抽样数据

为了演示视图是如何工作的，以及它们如何简化你的T-SQL代码，我需要一些测试数据来运行这些视图。与其创建我自己的测试数据，我的大多数示例将使用AdventureWorks2008R2数据库。如果你希望继续学习并在您的环境中运行我的示例，那么您可以从这里下载AdventureWorks2008R2数据库（然后这是一个网站）

 

使用视图简化SQL代码的示例

通过使用视图你可以返回列的列表，这些列是表列的子集、来自多个表的一组列、基于某些WHERE条件的一组受约束的列，或者是一个满足其他不同要求的数字。在本章节中，我将提供许多不同的示例，说明如何使用视图来满足不同的需求。

 

对于我的第一个示例，让我们假设不需要将单个表中的所有列呈现给应用程序或临时查询。对于这个例子，让我们假设需人力资源表中的个人信息，如清单1所示。(注：AdventureWorks2008R2数据库中已有此表；此处列出的定义的目的是仅供参考。

 

CREATE TABLE [HumanResources].[Employee](

[BusinessEntityID] [int] NOT NULL,

[NationalIDNumber] [nvarchar](15) NOT NULL,

[LoginID] [nvarchar](256) NOT NULL,

[OrganizationNode] [hierarchyid] NULL,

[OrganizationLevel]  AS ([OrganizationNode].[GetLevel]()),

[JobTitle] [nvarchar](50) NOT NULL,

[BirthDate] [date] NOT NULL,

[MaritalStatus] [nchar](1) NOT NULL,

[Gender] [nchar](1) NOT NULL,

[HireDate] [date] NOT NULL,

[SalariedFlag] [dbo].[Flag] NOT NULL,

[VacationHours] [smallint] NOT NULL,

[SickLeaveHours] [smallint] NOT NULL,

[CurrentFlag] [dbo].[Flag] NOT NULL,

[rowguid] [uniqueidentifier] ROWGUIDCOL  NOT NULL,

[ModifiedDate] [datetime] NOT NULL);

 

表1：HumanResources.Employee表的表定义

应用程序和临时用户需要的非个人信息如下：企业单位、NationalIDNumber（有理数是什么鬼？）、登录ID、组织点、组织水平、职务头衔和雇用日期。

 

为了创建一个只返回HumanResources.Employee表中列子集的视图，我将使用表2中的代码。

CREATE VIEW [HumanResources].[EmployeeInfo]

AS

SELECT [BusinessEntityID]

      ,[NationalIDNumber]  

      ,[LoginID]  

      ,[OrganizationNode]  

      ,[OrganizationLevel]

      ,[JobTitle]

      ,[HireDate]  

      ,[CurrentFlag]  

FROM [HumanResources].[Employee];

 

表2：从HumanResources.Employee表创建非个人信息视图的脚本

通过查看清单2中的创建视图语句，可以看出这很简单。视图的代码只是一个简单的SELECT语句，它包含了我希望视图在本身中公开的列在选择的标准列里。一旦我创建了这个视图，我就可以像普通的表一样查询它。清单3中的脚本演示了从HumanResources检索数据的两个不同的SELECT语句humanresourse .Employee表使用清单2中的代码创建的视图。

 

SELECT * FROM [HumanResources].[EmployeeInfo];

SELECT * FROM [HumanResources].[EmployeeInfo]

WHERE JobTitle like '%Manager%';

 

表3：使用视图返回数据的两个select语句

通过查看清单3中的代码，可以看到在FROM子句之后引用的对象是我在清单2中创建的视图的名称。我引用了SELECT语句中的视图，就像引用参考的一张表，表3中的第一个SELECT语句返回了HumanResources.Employee表中的所有行，但只返回了中SELECT子句中的那些新的非个人列。表3中的第二个SELECT语句演示了我可以使用WHERE语句约束返回的行，就像引用表时一样。

 

有时，数据库设计相当复杂，这可能会使构建查询变得复杂，从而访问数据库中所需的数据。这些复杂的设计可能需要复杂的多表连接来执行。 把数据还给我。这就是视图可以帮助的地方。通过使用视图，可以在视图中构建复杂的多表连接，然后使用视图查询数据。通过这样做，您可以简化代码用于查询数据库，并在视图中隐藏数据库设计的复杂性。为了演示这一点，我创建了一个视图4，它检索包含在多个表中的销售订单数据。

 

CREATE VIEW SalesOrderCombined2005

AS

SELECT

 OH.SalesOrderID

,OH.OrderDate

,OH.ShipDAte

,ST.Name AS TerritoryName

,BTA.City AS BillToCity

,STA.City AS ShiptToCity

,OH.TotalDue

FROM Sales.SalesOrderHeader OH

 JOIN Sales.SalesTerritory ST

 ON OH.TerritoryID = ST.TerritoryID

 JOIN Person.Address BTA

 ON OH.BillToAddressID = BTA.AddressID

 JOIN Person.Address STA

 ON OH.ShipToAddressID = STA.AddressID

WHERE YEAR(OH.OrderDate) = 2005;

 

表4：包含多表连接的视图

表4中的SalesOrderCombined 2005视图将多个表连接在一起，并且只从这些表返回一个列的子集。此外，视图还有WHERE子句。仅限WHERE条款 如果数据与2005年下的销售订单相关，则返回数据。此视图无需理解如何使用不同的键列将多个表连接在一起。通过 对SalesOrderCombined2005视图执行SELECT语句，所有这些联接都完成，而不必在SELECT语句中引用它们。通过放置复杂的联接语法 可以简化从复杂数据库设计中检索数据的代码。此外，这些类型的视图确保对数据库的所有查询都将使用相同的联接语法。通过提供和使用视图查询您的数据，您可以消除不正确写入连接条件的可能性。

有时你希望随着时间的推移演化数据库设计，但不想破坏现有代码。一个视图可以处理满足这个业务需求。为了证明这一点，回顾一下表5中的代码。

 

Begin Old Schema

CREATE TABLE DateDimOld (

ID INT IDENTITY,

DT DATE,

DOWTitle varchar(10));

GO

-- Populate DateDimOld

INSERT INTO DateDimOld(DT, DOWTitle) VALUES

  ('12/1/2013',DATENAME(DW,'12/1/2013')),

  ('12/2/2013',DATENAME(DW,'12/2/2013')),

  ('12/3/2013',DATENAME(DW,'12/3/2013'));

GO

SELECT * FROM DateDimOld;

GO

--- End Old Schema  

--  Begin New Schema  

CREATE TABLE DOWTitle (

DowTitleID INT IDENTITY PRIMARY KEY,

DOWTitle VARCHAR(10));

GO

CREATE TABLE DateDimNew (

ID INT IDENTITY,

DT DATE,

DOWTitleID INT);

GO

ALTER TABLE DateDimNew  WITH CHECK ADD  CONSTRAINT [FK_DateDimNew_DOWTitle_DOWTitleID] FOREIGN KEY(DOWTitleID)

REFERENCES DOWTitle (DOWTitleID)

GO

-- Populate DOWTitle

INSERT INTO DOWTitle (DOWTitle) VALUES

  (DATENAME(DW,'12/1/2013')),

  (DATENAME(DW,'12/2/2013')),

  (DATENAME(DW,'12/3/2013'));

GO

-- Populate DateDimNew

INSERT INTO DateDimNew (DT,DOWTitleID) VALUES

  ('12/1/2013', 1),

  ('12/2/2013', 2),

  ('12/3/2013', 3);

GO

-- Remove Old Schema

DROP TABLE DateDimOld

GO

-- Create view to similate Old Schema

CREATE VIEW DateDimOld AS

SELECT DDN.ID, DDN.DT, DOWT.DOWTitle

FROM DateDimNew AS DDN

JOIN DOWTitle AS DOWT

ON DDN.DOWTitleID = DOWT.DowTitleID;

GO

-- Show how VIEW and Simulate Old Schema

SELECT * FROM DateDimOld

-- End New Schema

 

表5：新旧模式结构

通过查看表5中的代码，可以看到代码有两个不同的部分。在第一节中，我定义、填充并显示了一些来自具有单个表n的旧模式的数据datedimOLD此表包含名为DT的日期列和名为DOWTitle的“一周中的一天”列，并将这些列关联到ID列。在第二节中，我定义了一个新的模式。 若要替换第一节中的旧架构，请执行以下操作。在第二节中，我创建了两个表。第一个表名为DOWTitle，它包含DOWTitle和DOWTitleID列。第二个表名为 DateDimNew.

此表包含ID、DT和DOWTitleID列。DOWTitleID列是DOWTitle表中的外键列。这个新模式是一个规范化的模式，而旧模式是一个DENOR mizedschema在代码的第二部分中，我实际上删除了在第一部分代码中创建的表，并创建了一个同名的视图DateDimold。DateDimold视图允许我查询t 他新的规范化模式就像我在旧模式中查询DateDimold表一样。这个新视图DateDimold允许我为可能构建的任何代码提供向后兼容性因为它使用旧的架构设计。

正如您所看到的，可以使用多种不同的视图。在我的例子中，我只演示了从视图中选择数据。视图也可以用于更新表。此外，下面是创建视图时可以使用的其他选项。

 

更新视图的底层表

视图也可用于更新表中的数据。为了演示这一点，我将运行表6中的代码。

 

INSERT INTO DateDimOld (DOWTitle)

VALUES (DATENAME(DW,'12/4/2013'));

 

表6：使用视图将数据插入底层表

 

表6中的代码并没有真正更新DateDimold表(反正已经删除了)，而是更新了基础表DOWTitle，它是DateDimold视图定义的一部分。 在运行表6中的INSERT语句之后，在DOWITITY表中创建了一行，其中包含DOWTitle列中的“星期三”值。因为DATEDIMOLD是我规范化日期的视图 维度表我还需要在表DateDimNew中放置另一行，以便DateDimold视图显示“星期三”值。为此，我运行表7中的代码。

 

INSERT INTO DateDimNew (DT, DOWTitleID)

   SELECT '12/4/2013', DOWTitleID FROM DOWTitle

       WHERE DOWTitle = DATENAME(DW,'12/4/2013');

 

清单7：向DateDimNew表添加一行

因为DOWTitleID列不是DateDimold视图的一部分，所以我无法使用该视图更新DateDimNewtable。相反，我必须编写清单7中的代码来引用底层视图替换图表。

在使用视图更新视图的基础表方面存在一些限制。以下是这些限制：

 

1.只能更新视图中的单个基础表。

2.正在更新的列必须在视图中直接引用，而不对其进行任何计算。

3.被修改的列不受组的影响，不受子句的影响。

在使用CHECK选项(下面更多有关此选项)时，视图不包含top子句

有关限制的更多信息，请参阅文档。

确保视图不受其他表更改或更新的影响

在我到目前为止展示的CREATEVIEW语句中，创建的视图不会限制对基础表所能做的事情。您可以对视图表进行一些更改。视图使用可能会破坏视图，或者返回意外的结果。一个这样的改变会打破视图，放弃视图引用的列。有些情况下你可能想做某事。 你的观点不受这些问题的影响。创建视图时，可以在Create视图或SELECT语句上添加几个额外子句，以帮助减少令人烦恼的潜在问题。

 

我们可以做的第一件事是绑定视图基础表模式。通过将表绑定到基础架构，可以限制可能破坏视图的任何表更改。证明我可以运行表8中的代码。

ALTER VIEW DateDimOld WITH SCHEMABINDING AS

SELECT DDN.ID, DDN.DT, DOWT.DOWTitle

FROM dbo.DateDimNew AS DDN

JOIN dbo.DOWTitle AS DOWT

ON DDN.DOWTitleID = DOWT.DowTitleID;

GO

 

表8：创建一个带有模式绑定的视图

 

在表8中，我删除并重新创建了DateDimold视图。当我重新创建它时，我添加了WITH SCHEMABINDING子句。这创建了架构绑定视图。当我做更改时，我还必须修改视图中选择语句。我所做的改变是为所有表都取了两个部件名。建议在引用SQLServer表时始终使用两个部件命名 SQL Server技术上是否需要它。这个要求意味着我必须在原始视图中的两个表名前面添加“DBO”。除此之外，这个观点与以前完全相同，起初，为了显示模式绑定如何限制对底层表的操作，让我运行表9中的代码。

 

 

ALTER TABLE dbo.DateDimNew

  ALTER COLUMN DT INT;

 

表9：尝试修改带有模式绑定的表

当运行表9中的代码时，我会得到报告1中显示的错误。

Msg 5074, Level 16, State 1, Line 1The object 'DateDimOld' is dependent on column 'DT'.Msg 4922, Level 16, State 9, Line 1ALTER TABLE ALTER COLUMN DT failed because one or more objects access this column.

 

图表1：更改架构绑定视图的列时接收到的错误

 

通过查看Report 1中的输出，可以看到数据库引擎阻止我修改DT列，该列包含在视图定义中。通过创建模式绑定视图，我确保了其他人不会出现并修改表中可能影响我的DateDimold视图的任何部分。

创建视图的另一个可用选项是WITH CHECK选项。WITH CHECK选项允许您在视图上设置约束，以确保对基础表的任何更新都是selec。 使用视图的表。要展示WITH CHECK选项是如何使用的，请查看表10中的代码。

 

CREATE TABLE DayOfTheWeek(DayOfTheWeek varchar (10),

              DayOfTheWeekNum int);

INSERT INTO  DayOfTheWeek VALUES

    ('Monday',0),

    ('Tuesday',1),

    ('Wednesday',2),

    ('Thursday',3),

    ('Friday',4);

GO

CREATE VIEW DisplayDayOfTheWeek

AS

SELECT DayOfTheWeek, DayOfTheWeekNum FROM DayOfTheWeek

WHERE DayOfTheWeekNum < 5

WITH CHECK OPTION;

 

表10：创建带有CHECK选项的视图

 

在表10中的代码中，可以看到我创建了一个表并填充了一个名为DayOfTheWeek的表。我还创建了一个名为DisplayDayOfTheWeek的视图，它通过使用WHERCLA限制返回的天数 使用，我添加了WITH CHECK选项。通过添加WITH CHECK选项，SQLServer将不允许我使用DisplayDayOfTheWeek视图插入或更新行，除非DayOfTheWeekNum值为少于5。为了测试这一点，我可以运行表11中的代码。

 

INSERT INTO  DisplayDayOfTheWeek VALUES

    ('Saturday',5);

UPDATE DisplayDayOfTheWeek

SET DayOfTheWeekNum = 5

WHERE DayOfTheWeek = 'Friday';

 

表11：测试WITH CHECK选项的代码

 

当表11中的代码试图插入值大于5的新行时，或者将现有的星期五行更新为DayOfTheWeekNum值大于5时，我将得到报告2中显示的错误。在清单11中的代码将生成此消息两次，一次用于插入，一次用于更新。

 

The attempted insert or update failed because the target view either specifies WITH CHECK OPTION or spans a view that specifies WITH CHECK OPTION and one or more rows resulting from the operation did not qualify under the CHECK OPTION constraint.

The statement has been terminated.

 

Report 2：测试WITH CHECK选项的代码

 

通过查看消息，可以看到WITH CHECK选项导致表11中的INSERT和UPDATE语句失败。如果要实际插入或更新这些行，则有两个选项。一次行动 条件是删除WITH CHECK选项。Thi允许您通过视图更改基础表，但从视图中进行选择仍然不能显示满足Condi的值。 视图定义中的条件。如果您希望插入和更新这些行并让视图显示它们，那么第二个选项是更改视图中的WHERE条件，以允许新的数值 被选中。(请记住，WITCHECK选项仅适用于通过视图所做的更改；这并不阻止直接对基础表进行更新或插入。

 

如果希望控制可能影响视图的语句类型，则应考虑使用模式绑定和/或WITH CHECK选项。

 

使用视图时的性能考虑

使用视图是否存在性能问题？

与大多数SQLServer问题一样，答案是“它取决于”。(我也不确定是不是这么翻译）

视图的性能将取决于视图正在做什么。读取一个没有联接子句的表的简单视图很可能执行与只引用SING的查询非常相似。 桌子。但是，如果您有一个引用视图的视图，并且这些视图包含多个联接子句，怎么办？实际由简单select执行的底层查询 引用视图的select语句可能会爆炸成一个非常复杂的SELECT语句，包含多个JOIN子句，并可能最终完成比预期的更多的工作。

 

关于视图的性能问题，还有一点值得一提的是，当视图包含多个连接在一起的表时，您只想从视图中的单个表返回数据。在 ISCase SQLServer仍然必须连接视图中的所有表才能从单个表返回数据。这会导致SQLServer在视图中加入所有这些表的额外工作，而且速度更慢。 只希望从视图中的单个表返回数据的查询的响应时间。如果您发现您只会从视图中的单个表返回数据，那么性能就很重要。 那么，最好是编写查询以针对单个表，而不是使用包含多个表联接的视图。

视图是简化代码和隐藏数据库模式复杂性的好方法。但是隐藏这种复杂性会导致严重的性能问题。如果计划使用视图，请确保了解where的属性和功能。在了解查询引擎对视图执行查询所必须做的工作，将有助于开发性能良好的代码。

 

使用视图保护数据

人们使用视图的另一个原因是保护对表中某些列的访问。假设您有一个业务要求允许用户对包含机密数据的表进行报告， 比如社会保险号码或者信用卡号码。您可能不希望他们访问这些机密列。确保他们不能读取这些机密数据列的一种方法是 查看排除这些机密列的表视图，而不提供用户在基础表上的选择权限。

 

一言以蔽之：

视图是实现安全、简化查询复杂数据库模式和/或提供向后功能的重要方式。

但是如果你在没有联合国的情况下开始建国（？？？），那会有一个邪恶的一面，无视这可能造成的性能影响。当您查看需要T-SQL解决方案的给定业务需求时，可以将视图视为可能用于解决方案的许多工具之一。