Stairway to T-SQL: Beyond The Basics Level 4: Using Views to Simplify Your Query - SQLServerCentral

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

什么是视图？

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

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

• 您不希望向查询表的用户公开表的所有列。
• 您的数据库架构设计很复杂，因此您可以构建视图以简化用户访问。
• 您希望更改数据库架构设计，但希望保持向后兼容性，这样就不必重写现有代码。

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

样本数据

为了演示视图是如何工作的，以及它们如何简化您的T-SQL代码，我需要一些测试数据来运行这些视图。与其创建自己的测试数据，我的大多数示例将使用AdventureWorks2008R2数据库。如果您想在您的环境中继续并运行我的示例，则可以下载AdventureWorks2008R2数据库：http://msftdbprodsamples.codeplex.com/releases/view/93587

使用视图简化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表的表定义

应用程序和临时用户需要的非个人信息如下：BusinessEntityId，NationalIDNumber，LoginID，OrganizationNode，OrganizationLevel，职称，和希雷达特.

以创建只返回列子集的视图。人力资源.雇员表我将使用清单2中的代码。

CREATE VIEW [HumanResources].[EmployeeInfo]
AS
	SELECT [BusinessEntityID] 
	      ,[NationalIDNumber]  
	      ,[LoginID]  
	      ,[OrganizationNode]  	
	      ,[OrganizationLevel]
	      ,[JobTitle] 
	      ,[HireDate]  
	      ,[CurrentFlag]  
    FROM [HumanResources].[Employee];

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

通过查看清单2中的CREATEVIEW语句，您可以看到它非常简单。视图的代码只是一个简单的SELECT语句，它包含了我希望视图在选择条件中公开的列。一旦我创建了这个视图，我就可以像普通的表一样查询它。清单3中的脚本演示了从人力资源.雇员表，使用清单2中的代码创建的视图。

SELECT * FROM [HumanResources].[EmployeeInfo];
SELECT * FROM [HumanResources].[EmployeeInfo]
	WHERE JobTitle like '%Manager%';

清单3：使用视图返回数据的两个SELECT语句

通过查看清单3中的代码，您可以看到在FROM子句之后引用的对象是我在清单2中创建的视图的名称。我引用了SELECT语句中的视图，就像引用表一样。清单3中的第一个SELECT语句返回了人力资源表中，但仅返回我视图中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：包含多表连接的视图

大销售订单组合2005清单4中的视图将多个表连接在一起，并且只返回这些表中列的子集。此外，视图还有WHERE子句。WHERE子句只返回与2005年下的销售订单相关的数据。此视图无需理解如何使用不同的键列将多个表连接在一起。对象执行SELECT语句。销售订单组合2005查看所有这些联接都是完成的，而不必在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中的代码，您可以看到代码有两个不同的部分。在第一节中，我定义、填充和显示了一个旧模式中的一些数据，该模式有一个名为戴蒙德。此表包含两个日期列，名为DT，以及一个名为“一周中的一天”的专栏文章多沃泰尔并将这些列关联到ID列。在第二节中，我定义了一个新模式，以取代第一节中的旧模式。在第二节中，我创建了两个表。第一个表名为多沃泰尔，其中包含多沃泰尔和DOWTitleID柱。第二个表名为戴蒙德。此表包含身份证，DT，和DOWTitleID柱子。大DOWTitleID列中的外键列。多沃泰尔桌子。这个新架构是一个规范化模式，而旧模式是一个非规范化架构。在代码的第二部分中，我实际上删除了在第一部分代码中创建的表，并创建了一个名称相同的视图，戴蒙德。大戴蒙德视图允许我查询新的规范化架构，就像查询戴蒙德旧架构中的表。这种新观点戴蒙德允许我为使用旧模式设计的任何代码提供向后兼容性。

正如您所看到的，视图可以使用许多不同的方式。在这里的示例中，我只向您展示了从视图中选择数据。视图也可以用于更新表。此外，在创建视图时还可以使用其他选项。

更新视图的底层表

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

INSERT INTO DateDimOld (DOWTitle) 
VALUES (DATENAME(DW,'12/4/2013'));

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

清单6中的代码并没有真正更新戴蒙德表(反正已经删除了)，但是更新了基础表。多沃泰尔的视图定义的一部分。戴蒙德。运行清单6中的INSERT语句后，将在多沃泰尔表中包含“星期三”值的表。多沃泰尔列。因为戴蒙德是规范化日期维度表的视图，我还需要在表中放置另一行。戴蒙德为了视图戴蒙德若要显示“星期三”值，请执行以下操作。为此，我运行清单7中的代码。

INSERT INTO DateDimNew (DT, DOWTitleID) 
   SELECT '12/4/2013', DOWTitleID FROM DOWTitle 
       WHERE DOWTitle = DATENAME(DW,'12/4/2013');

清单7：向戴蒙德表

因为列DOWTitleID不是戴蒙德视图无法使用该视图更新戴蒙德桌子。相反，我必须编写清单7中的代码来直接引用基础视图表。

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

• 只能更新视图中的单个基础表。
• 正在更新的列必须在视图中直接引用，而不对其进行任何计算。
• 被修改的列不受组的影响，不受子句的影响。
• 在使用CHECK选项(下面更多有关此选项)时，视图不包含top子句

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

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

在我到目前为止向您展示的CREATEVIEW语句中，创建的视图不会限制您对底层表所能做的事情。您可以对视图使用的基础表进行一些更改，这些更改可能会破坏视图，或者返回意外的结果。其中一个会破坏视图的更改是删除视图引用的列。在某些情况下，您可能希望确保您的观点不受此类问题的影响。创建视图时，可以在CREATEVIEW或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子句。这创建了架构绑定视图。当我做这个更改时，我还必须稍微修改视图中的SELECT语句。我所做的改变是为所有表都取了两个部件名。建议在引用SQLServer表时始终使用两个部件命名，无论SQLServer技术上是否需要它。这个要求意味着我必须在原始视图中的两个表名前面添加“DBO”。除此之外，这个观点和原来的完全一样。

为了显示模式绑定如何限制对底层表的操作，让我运行清单9中的代码。

ALTER TABLE dbo.DateDimNew 
  ALTER COLUMN DT INT;

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

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

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

报表1：更改模式绑定视图的列时收到的错误

通过查看Report 1中的输出，您可以看到数据库引擎阻止我修改DT列，该列包含在视图定义中。通过创建模式绑定视图，我确保没有人出现，并修改了可能影响到戴蒙德视野。

创建视图的另一个可用选项是WITH CHECK选项。WITH CHECK选项允许您在视图上设置约束，以确保对底层表的任何更新都可以使用视图进行选择。要向您展示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这限制了使用WHERE子句返回的天数，并添加了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的新行时，或将现有的星期五行更新为DayOfTheWeek努姆值大于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允许您通过视图更改基础表，但从视图中进行选择仍然不能显示那些符合视图定义条件的值。如果希望插入和更新这些行并让视图显示它们，则第二个选项是更改视图中的WHERE条件，以允许选择新值。(请记住，WITCHECK选项仅适用于通过视图所做的更改；IS并不阻止直接对基础表进行更新或插入。

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

使用视图时的性能考虑

使用视图是否存在性能问题？与大多数SQLServer问题一样，答案是“它取决于”。

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

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

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

使用视图保护数据

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

摘要

视图是实现安全性、简化复杂数据库架构查询和/或提供反向功能的一种很好的方法。但是，如果您开始嵌套视图，而不了解这可能造成的性能影响，那么视图就有其邪恶的一面。当您查看需要T-SQL解决方案的给定业务需求时，请将视图视为您可能用来实现解决方案的许多工具之一。

问答

在本节中，您可以通过回答以下问题来查看如何使用视图查询数据库。

问题1：

什么是好的业务需求，视图可以帮助您实现？

• 需要防止应用程序或临时查询访问表中的底层列。
• 需要简化查询复杂数据库结构所需的代码。
• 需要提供向后兼容性。
• 以上所有

问题2：

您需要确保在通过视图更新或插入列值时，它是可以通过视图选择的。哪个子句提供此功能？

• 创建视图
• 用SCHEMABINDING
• 带检查选项
• 以上都不是

问题3：

您需要限制对表中机密数据的访问。可以使用什么方法来限制对此数据的访问？

• 使用WITH CHECK选项创建视图
• 创建一个使用WITH SCHEMABINDING选项的视图
• 创建一个视图，该视图将表中的机密列排除在外，但未证明对表的选择访问权限。
• 创建一个视图，该视图排除表中的机密列，并验证对表的选择访问权。

回答：

问题1：

答案是d，在直接查询上使用视图有很多原因。a、b和c是其中一些原因。

问题2：

正确的答案是c。CREATE视图不是提供任何其他数据完整性检查的子句。WITH SCHEMABINDING子句确保在更改视图的底层表结构时，任何ALTERTABLE语句都不会导致视图问题。只有WITH CHECK选项才能确保您不能更新底层表，除非这些更改可以立即使用视图进行查询。

问题3：

正确的答案是c。答a和b不具体限制对机密列的访问，因为它们没有提到将机密列排除在视图之外。答案d是不正确的，因为如果人们可以访问包含机密数据的底层表，那么他们仍然可以通过编写直接针对该表的查询来选择机密列。