使用视图简化T-SQL查询-CSDN博客

到T-SQL的阶梯：超越基础4级：使用视图简化查询

此系列

本文是阶梯系列的一部分：通往T-SQL的阶梯：超越基础

从他的阶梯到T-SQL DML之后，Gregory Larsen涵盖了T-SQL语言的更高级的方面，如子查询。

在这个阶梯级别，将讨论如何使用数据库视图来简化Transact-SQL（T-SQL）代码。通过理解如何使用视图，你将能够更好地支持编写T-SQL代码以满足复杂的业务需求。在本文中，我将讨论数据库视图是什么，然后提供一些示例来帮助你理解如何使用视图来实现不同的编码方案。

什么是观点？

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

这里有些情况下，视图可以帮助你编写编码逻辑：

你不想将表的所有列暴露给查询表的用户。

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

你希望更改数据库架构设计，但希望保持向后兼容性，因此不必重写现有代码。

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

样本数据

为了演示视图是如何工作的，以及它们如何简化你的T-SQL代码，我需要一些测试数据来支持这些视图。而不是创建自己的测试数据，我的大多数例子将使用AdvultWorks200 8R2数据库。如果你想跟上并在你的环境中运行我的例子，那么你可以从这里下载AdvyWorkss88R2数据库：http://msftdbprodsamples.codeplex.com/releases/view/93587

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

通过使用视图，可以返回列表的子集列表、来自多个表的列集、基于某个标准的约束列集或多个其他不同的需求。在这一节中，我将提供一些不同的例子，使用一个视图来满足不同的业务需求。

对于我的第一个例子，假设你有一个要求，不将单个列中的所有列呈现给应用程序或自组织查询。对于这个例子，假设你只想从清单1所示的HealthReals.Effice表中返回非个人信息。（注意，这个表已经在AdvesturWorks208R2数据库中显示了；该定义仅在这里列出，仅供参考。）

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：HealthReals.Effice表的定义

应用程序和特定用户需要的非个人信息是以下列：BuleTeaTyId、NealAldIdIt、LogiID、组织节点、OrganizationLevel、JobTitle和HeReDebug。

为了创建只从人力资源.Effice表返回列的子集的视图，我将使用清单2中的代码。

CREATE VIEW [HumanResources].[EmployeeInfo]

AS

        SELECT [BusinessEntityID]

              ,[NationalIDNumber]

              ,[LoginID]

              ,[OrganizationNode]

              ,[OrganizationLevel]

              ,[JobTitle]

              ,[HireDate]

              ,[CurrentFlag]

    FROM [HumanResources].[Employee];

清单2：从人力资源表中创建非个人信息视图的脚本。

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

SELECT * FROM [HumanResources].[EmployeeInfo];

SELECT * FROM [HumanResources].[EmployeeInfo]

        WHERE JobTitle like '%Manager%';

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

通过查看清单3中的代码，可以看到在OFF子句后面引用的对象是清单2中创建的视图的名称。我引用SELECT语句中的视图，就像引用一个表一样。清单3中的第一个SELECT语句返回了我的HealthReals.Effice表中的所有行，但只返回了我的视图中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中的SaleOrgCudio2005视图将多个表连接在一起，并且只返回这些表中的一个子集。另外，视图具有WHERE子句。WHERE子句仅返回与2005年度销售订单相关的数据。此视图消除了了解如何使用不同的键列将多个表连接在一起的必要性。通过对SaleOrthCuuleDe2005视图执行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中的代码，你可以看到代码有两个不同的部分。在第一部分中，我定义、填充和显示了来自一个具有DateDimOld的单个表的旧模式的一些数据。此表既包含日期列名为dt，又包含一周名为DOWTitle的列，并将这些列关联为ID列。在第二节中，我定义了一个新的模式来替换第一节中的旧模式。在第二节中，我创建了两个表。第一个表名为DOWTITY，其中包含DOWTITLE和DOWTITLE ID列。第二张桌子名叫DateDimNew。此表包含ID、DT和DoWITILID ID列。DoWiTeLeID列是DoWITH表中的外键列。这个新的模式是一个规范化的模式，而旧的模式是一个非规范化的模式。在代码的第二部分中，我实际上放弃了在代码的第一部分中创建的表，并创建了具有相同名称的视图DateDimOld。DATEDIMORD视图允许我查询新的规范化模式，就像查询旧模式中的DATEDIMORD表一样。这个新视图DATEDIMORD允许我为任何可能使用旧模式设计的代码提供向后兼容性。

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

更新视图的基础表

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

INSERT INTO DateDimOld (DOWTitle)

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

清单6：使用视图将数据插入基础表

清单6中的代码没有真正更新DATEDIMORD表（它已经被删除），而是更新基础表DOWTITY，它是DATEDIMORD的视图定义的一部分。在运行清单6中的INSERT语句之后，在DOWITITY表中创建了一行，其中包含DOWTitle列中的“星期三”值。由于DATEDIMORD是我规范化的日期维度表的一个视图，所以我需要在表DATEIMDIMULD中放置另一行，以便查看DATEDIMOLD显示“星期三”值。为此，我运行清单7中的代码。

INSERT INTO DateDimNew (DT, DOWTitleID)

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

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

清单7：向DATEIMDIME表添加行

因为列DOWTITLE ID不是DATEDIMORD视图的一部分，所以我不能使用视图来更新DATEIMDIONE表。相反，我必须编写清单7中的代码来直接引用基础视图表。

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

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

更新的列必须在视图中直接引用，而不需要对它们进行任何计算。

修改的列不受分组、区分或拥有子句的影响。

当选中选项（在下面的选项上更多）时，你的视图不包含顶部子句。

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

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

在我已经向你展示的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中，我删除并重新创建DATEDIMORD视图。当我重新创建它时，我添加了Studiabin的子句。这创建了模式绑定视图。当我做了更改时，我还必须稍微修改视图中的SELECT语句。我所做的更改是对所有表有两个部分名称。建议在引用SQL Server表时总是使用两个部分命名，而不管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：更改架构绑定视图的列时接收到的错误

通过查看报表1中的输出，可以看到数据库引擎使我不必修改DT列，而DT列包含在视图定义中。通过创建一个模式绑定视图，我确保了某些人不会出现并修改可能影响DATEDIMORD视图的表的任何部分。

创建视图时可用的另一个选项是“随选中”选项。使用“检查”选项允许你在视图上设置约束，以确保使用基础视图对基础表进行的任何更新都是可选择的。要显示如何使用“检查”选项，请查看清单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：创建带有检查选项的视图

在清单10中的代码中，你可以看到我创建了一个表并填充了一个名为DayOfTheWeek的表。我还创建了一个名为DePaseDayFoFeX的视图，它限制了使用WHERE子句返回的日期，并添加了“检查”选项。通过添加“带检查”选项，SQLServer将不允许我使用DePayDayOfTew视图插入或更新行，除非DayOfthWeeNuMUM值小于5。为了测试这一点，我可以运行清单11中的代码。

INSERT INTO  DisplayDayOfTheWeek VALUES

    ('Saturday',5);

UPDATE DisplayDayOfTheWeek

SET DayOfTheWeekNum = 5

WHERE DayOfTheWeek = 'Friday';

清单11：测试带有检查选项的代码

当清单11中的代码试图插入一个值大于5的新行时，或者更新我现有的星期五行到一个大于5的DayOfWeeKNUM值时，我得到了报表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.

报表2：测试带有检查选项的代码

通过查看消息，你可以看到使用“检查”选项导致清单11中的INSERT和UPDATE语句失败。如果要实际插入或更新这些行，则有两个选项。一个选项是移除带有检查选项的选项。THS允许你通过视图更改基础表，但是从视图中选择仍然不会显示满足视图定义中条件的值。如果希望插入并更新这些行并使它们显示在视图中，那么第二个选项是更改视图中的WHERE条件，以允许选择新的值。（请记住，使用检查选项只适用于通过视图进行的更改；IS不阻止直接更新到基础表的插入或插入。

如果要控制可能影响视图的语句类型，则应考虑使用架构绑定和/或使用检查选项。

使用视图时的性能考虑

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

视图的性能将取决于视图所做的操作。一个简单的视图，读取一个没有联接子句的单个表将很可能与引用单个表的查询非常类似。但是，如果有一个视图引用引用视图的视图，那么这些视图包含多个连接子句呢？由引用视图的简单SELECT语句实际执行的基础查询可能会爆炸成具有多个联接子句的非常复杂的SELECT语句，最终可能会比预期的要做更多的工作。

关于视图的性能问题，值得注意的是，当视图包含多个表连接在一起时，你只想从视图中的单个表返回数据。在这种情况下，SQLServer仍然需要加入视图中的所有表以从单个表返回数据。这会导致SQLServer添加额外的工作，以便在视图中加入所有这些表，对于那些只想从视图中的单个表返回数据的查询，响应时间较慢。如果发现只从视图中返回单个表中的数据，性能很重要，那么最好将查询写入单个表，而不是使用包含多个表连接的视图。

CVES是一种简化代码并隐藏数据库模式复杂性的好方法。但是隐藏这种复杂性会导致严重的性能问题。如果你计划使用视图，请确保你知道视图在幕后所做的事情。了解查询引擎将不得不执行的任务来执行对视图的查询，将有助于开发执行良好的代码。

使用视图保护数据

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

总结

视图是实现安全性、简化查询复杂数据库模式和/或提供向后能力的一种很好的方式。但是如果你开始嵌套视图而不理解这可能导致的性能影响，那就有一个邪恶的观点。当你查看需要T-SQL解决方案的特定业务需求时，将视图视为你可以用来实现解决方案的众多工具中的一种。

问答

在本节中，你可以通过回答下列问题来查看你已经理解了如何使用视图来查询数据库。

问题1：

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

需要保持应用程序或自组织查询访问表中的基础列。

需要简化查询复杂数据库结构所需的代码。

需要提供向后兼容性。