创建索引视图-阿里云开发者社区

开发者社区> y0umer> 正文

创建索引视图

简介: SQL Server 2005 其他版本                SQL Server 2008 R2 SQL Server 2008             0(共 2)对本文的评价是有帮助 - 评价此主题 更新日期:           2006 年 12 月 12 日 在对视图创建聚集索引之前,该视图必须符合下列要求:  当执行 CREATE VIEW 语句时,ANSI_NULLS 和 QUOTED_IDENTIFIER 选项必须设置为 ON。
+关注继续查看
SQL Server 2005
            0(共 2)对本文的评价是有帮助 - 评价此主题

更新日期:           2006 年 12 月 12 日

在对视图创建聚集索引之前,该视图必须符合下列要求: 

  • 当执行 CREATE VIEW 语句时,ANSI_NULLS 和 QUOTED_IDENTIFIER 选项必须设置为 ON。OBJECTPROPERTY 函数通过 ExecIsAnsiNullsOnExecIsQuotedIdentOn 属性为视图报告此信息。
  • 要执行所有 CREATE TABLE 语句以创建视图引用的表,ANSI_NULLS 选项必须设置为 ON。
  • 视图不能引用任何其他视图,只能引用基表。
  • 视图引用的所有基表必须与视图位于同一数据库中,并且所有者也与视图相同。
  • 必须使用 SCHEMABINDING 选项创建视图。架构绑定将视图绑定到基础基表的架构。
  • 必须已使用 SCHEMABINDING 选项创建了视图引用的用户定义函数。
  • 表和用户定义函数必须由视图中由两部分组成的名称引用。不允许由一部分、三部分和四部分组成的名称引用它们。
  • 视图中的表达式引用的所有函数必须是确定的。OBJECTPROPERTY 函数的 IsDeterministic 属性报告用户定义函数是否具有确定性。有关详细信息,请参阅确定性函数和不确定性函数
    ms191432.note(zh-cn,SQL.90).gif注意:
    引用 SQL Server 2005 中的索引视图中的 datetimesmalldatetime 字符串文字时,建议使用确定性日期格式样式将文字显式转换为所需日期类型。有关确定性日期格式样式列表,请参阅 CAST 和 CONVERT (Transact-SQL)。将字符串隐式转换为 datetimesmalldatetime 所涉及的表达式被视为具有不确定性,除非兼容级别设置为 80 或更低。这是因为结果取决于服务器会话的 LANGUAGE 和 DATEFORMAT 设置。例如,表达式 CONVERT (datetime, '30 listopad 1996', 113) 的结果取决于 LANGUAGE 设置,因为字符串 listopad 在不同语言中表示不同的月份。同样,在 DATEADD(mm,3,'2000-12-01') 表达式中,SQL Server 基于 DATEFORMAT 设置解释 '2000-12-01' 字符串。

    非 Unicode 字符数据在排序规则间的隐式转换也被视为具有不确定性,除非兼容级别设置为 80 或更低。
    在 90 兼容模式下,不允许对包含这些表达式的视图创建索引。但是,包含已升级数据库中的这些表达式的现有视图是可维护的。如果使用索引视图(其中包含从字符串到日期的隐式转换),请确保 LANGUAGE 和 DATEFORMAT 的设置在数据库和应用程序中保持一致,以避免可能的索引视图损坏。
  • 如果视图定义使用聚合函数,SELECT 列表还必须包括 COUNT_BIG (*)。
  • 用户定义函数的数据访问属性必须为 NO SQL,外部访问属性必须是 NO。
  • 公共语言运行时 (CLR) 功能可以出现在视图的选择列表中,但不能作为聚集索引键定义的一部分。CLR 函数不能出现在视图的 WHERE 子句中或视图中的 JOIN 运算的 ON 子句中。
  • 在视图定义中使用的 CLR 函数和 CLR 用户定义类型方法必须具有下表所示的属性设置。

    属性              注意             

    DETERMINISTIC = TRUE

    必须显式声明为 Microsoft .NET Framework 方法的属性。

    PRECISE = TRUE

    必须显式声明为 .NET Framework 方法的属性。

    DATA ACCESS = NO SQL

    通过将 DataAccess 属性设置为 DataAccessKind.None 并将 SystemDataAccess 属性设置为 SystemDataAccessKind.None 来确定。

    EXTERNAL ACCESS = NO

    对于 CLR 例程,该属性的默认设置为 NO。

    有关如何设置 CLR 例程方法的属性的详细信息,请参阅 Custom Attributes for CLR Routines
    ms191432.Caution(zh-cn,SQL.90).gif注意:
    我们建议 CLR 例程方法的属性设置不要与该方法的功能相悖。如果相悖,可能会导致数据损坏。

  • 视图中的 SELECT 语句不能包含下列 Transact-SQL 语法元素:
    • 指定列的 * 或 table_name.* 语法。必须明确给出列名。
    • 不能在多个视图列中指定用作简单表达式的表列名。如果对列的所有(或除了一个引用之外的所有)引用是复杂表达式的一部分或是函数的一个参数,则可以多次引用该列。例如,下面的 SELECT 列表无效:
      SELECT ColumnA, ColumnB, ColumnA
      
      下面的 SELECT 列表有效: 
      SELECT SUM(ColumnA) AS SumColA, ColumnA % ColumnB AS ModuloColAColB, COUNT_BIG(*) AS cBig FROM dbo.T1 GROUP BY ModuloColAColB
      
    • 在 GROUP BY 子句中使用的列的表达式或基于聚合结果的表达式。
    • 派生表。
    • 公用表表达式 (CTE)。
    • 行集函数。
    • UNION、EXCEPT 或 INTERSECT 运算符。
    • 子查询。
    • 外联接或自联接。
    • TOP 子句。
    • ORDER BY 子句。
    • DISTINCT 关键字。
    • COUNT(*)(允许 COUNT_BIG(*)。)
    • AVG、MAX、MIN、STDEV、STDEVP、VAR 或 VARP 聚合函数。如果在引用索引视图的查询中指定了 AVG(expression),则当视图 SELECT 列表中包含 SUM(expression) 和 COUNT_BIG(expression) 时,优化器可经常计算所需结果。例如,索引视图 SELECT 列表不能包含表达式 AVG(column1)。如果视图 SELECT 列表包含表达式 SUM(column1) 和 COUNT_BIG(column1),则 SQL Server 可以计算引用视图并指定 AVG(column1) 的查询的平均数。
    • 引用可为空表达式的 SUM 函数。
    • 包括排名或聚合窗口函数的 OVER 子句。
    • CLR 用户定义聚合函数。
    • 全文谓词 CONTAINS 或 FREETEXT。
    • COMPUTE 或 COMPUTE BY 子句。
    • CROSS APPLY 或 OUTER APPLY 运算符。
    • PIVOT 或 UNPIVOT 运算符。
    • 表提示(仅应用于 90 或更高的兼容级别)。
    • 联接提示。
    • 对 Xquery 表达式的直接引用。可以接受间接引用,例如位于绑定到架构的、用户定义的函数内部的 Xquery 表达式。
  • 指定 GROUP BY 后,视图 SELECT 列表必须包含 COUNT_BIG(*) 表达式,并且视图定义不能指定 HAVING、CUBE 或 ROLLUP。

对视图创建的第一个索引必须是唯一聚集索引。创建唯一聚集索引后,可以创建其他非聚集索引。视图的索引命名约定与表的索引命名约定相同。唯一的区别是表名替换为视图名。有关详细信息,请参阅 CREATE INDEX (Transact-SQL)

CREATE INDEX 除了符合 CREATE INDEX 的常规要求之外,还必须符合下列要求: 

  • 执行 CREATE INDEX 语句的用户必须是视图所有者。
  • 执行 CREATE INDEX 语句时,下列 SET 选项必须设置为 ON:
    • ANSI_NULLS
    • ANSI_PADDING
    • ANSI_WARNINGS
    • CONCAT_NULL_YIELDS_NULL
    • QUOTED_IDENTIFIER
  • NUMERIC_ROUNDABORT 选项必须设置为 OFF。这是默认设置。
  • 如果数据库在 80 或更低的兼容模式下运行,则 ARITHABORT 选项必须设置为 ON。
  • 创建聚集索引或非聚集索引时,IGNORE_DUP_KEY 选项必须设置为 OFF(默认设置)。
  • 即使 CREATE INDEX 语句中未引用 textntext image 列,视图中也不能包含这些列。
  • 如果视图定义中的 SELECT 语句指定了一个 GROUP BY 子句,则唯一聚集索引的键只能引用在 GROUP BY 子句中指定的列。
  • 构成索引键列值的不精确表达式必须引用视图下基表中的存储列。该列可以是常规存储列,也可以是持久化计算列。其他不精确表达式不能作为索引视图的键列的一部分。

索引视图中列的 large_value_types_out_of_row 选项的设置继承的是基表中相应列的设置。此值是使用 sp_tableoption 设置的。由表达式组成的列的默认设置为 0。这意味着大值类型存储在行内。有关详细信息,请参阅使用大值数据类型

创建聚集索引后,对于任何尝试修改视图基本数据的连接,其选项设置必须与创建索引所需的选项设置相同。如果执行语句的连接的选项设置不正确,则 SQL Server 将生成错误,并回滚任何会影响视图结果集的 INSERT、UPDATE 或 DELETE 语句。有关详细信息,请参阅影响结果的 SET 选项

若删除视图,该视图的所有索引也将被删除。若删除聚集索引,视图的所有非聚集索引和自动创建的统计信息也将被删除。视图中用户创建的统计信息受到维护。非聚集索引可以分别删除。删除视图的聚集索引将删除存储的结果集,并且优化器将重新像处理标准视图那样处理视图。

尽管 CREATE UNIQUE CLUSTERED INDEX 语句仅指定组成聚集索引键的列,但视图的完整结果集将存储在数据库中。与基表的聚集索引一样,聚集索引的 B 树结构仅包含键列,但数据行包含视图结果集中的所有列。

若要向现有系统中的视图添加索引,必须架构绑定任何要放置索引的视图。您可以执行下列操作: 

  • 删除视图并通过指定 WITH SCHEMABINDING 重新创建视图。
  • 创建另一个视图,使其具有与现有视图相同的文本,仅名称不同。优化器将考虑新视图的索引,即使查询的 FROM 子句中没有直接引用它。
    ms191432.note(zh-cn,SQL.90).gif注意:
    不能删除参与到使用 SCHEMABINDING 子句创建的视图中的视图或表,除非创建的视图已被删除或更改而不再具有架构绑定。此外,如果 ALTER TABLE 语句会影响视图定义,则对参与具有架构绑定的视图的表执行这些语句将失败。

必须确保新视图符合索引视图的所有要求。这可能需要更改视图及其所引用的所有基表的所有权,以便它们属于同一用户。

可以禁用表和视图的索引。禁用表的聚集索引时,与该表关联的视图的索引也将被禁用。有关详细信息,请参阅禁用索引

以下示例将创建一个视图并为该视图创建索引。包含两个使用该索引视图的查询。

USE AdventureWorks;
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
    QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Create view with schemabinding.
IF OBJECT_ID ('Sales.vOrders', 'view') IS NOT NULL
DROP VIEW Sales.vOrders ;
GO
CREATE VIEW Sales.vOrders
WITH SCHEMABINDING
AS
    SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Revenue,
        OrderDate, ProductID, COUNT_BIG(*) AS COUNT
    FROM Sales.SalesOrderDetail AS od, Sales.SalesOrderHeader AS o
    WHERE od.SalesOrderID = o.SalesOrderID
    GROUP BY OrderDate, ProductID;
GO
--Create an index on the view.
CREATE UNIQUE CLUSTERED INDEX IDX_V1 
    ON Sales.vOrders (OrderDate, ProductID);
GO
--This query can use the indexed view even though the view is 
--not specified in the FROM clause.
SELECT SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev, 
    OrderDate, ProductID
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND ProductID BETWEEN 700 and 800
        AND OrderDate >= CONVERT(datetime,'05/01/2002',101)
GROUP BY OrderDate, ProductID
ORDER BY Rev DESC;
GO
--This query can use the above indexed view.
SELECT  OrderDate, SUM(UnitPrice*OrderQty*(1.00-UnitPriceDiscount)) AS Rev
FROM Sales.SalesOrderDetail AS od
    JOIN Sales.SalesOrderHeader AS o ON od.SalesOrderID=o.SalesOrderID
        AND DATEPART(mm,OrderDate)= 3
        AND DATEPART(yy,OrderDate) = 2002
GROUP BY OrderDate
ORDER BY OrderDate ASC;
GO

版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。

相关文章
阿里云服务器怎么设置密码?怎么停机?怎么重启服务器?
如果在创建实例时没有设置密码,或者密码丢失,您可以在控制台上重新设置实例的登录密码。本文仅描述如何在 ECS 管理控制台上修改实例登录密码。
9951 0
PostgreSQL 快速给指定表每个字段创建索引 - 1
标签 PostgreSQL , 索引 , 所有字段 , 并行创建索引 , max_parallel_maintenance_workers 背景 如何快速给表的所有字段,每个字段都加上索引。 《PostgreSQL 设计优化case - 大宽表任意字段组合查询索引如何选择(btree, gin, rum) - (含单个索引列数超过32列的方法)》 满足任意字段组合查询的需求。
1415 0
lucene创建索引
上篇博客写了信息检索的基本知识和lucene架构,这篇博客记录一下如何在eclipse中创建索引. 1.lucene下载.
522 0
Hive创建索引
Hive创建索引
2164 0
阿里云服务器如何登录?阿里云服务器的三种登录方法
购买阿里云ECS云服务器后如何登录?场景不同,阿里云优惠总结大概有三种登录方式: 登录到ECS云服务器控制台 在ECS云服务器控制台用户可以更改密码、更换系.
13699 0
PostgreSQL 快速给指定表每个字段创建索引 - 2
标签 PostgreSQL , 索引 , 所有字段 , 并行创建单个索引 , max_parallel_maintenance_workers , 异步调用 , dblink , 并行创建多个索引 , adhoc查询 背景 PostgreSQL 支持丰富的类型、索引,统计信息。
1029 0
sql创建临时表或视图增加 自增列
select row_number()over(order by [createtime] asc) ida,* from [Opro].[dbo].[DailyReport2010])
746 0
+关注
y0umer
高级网络安全技术员
906
文章
0
问答
文章排行榜
最热
最新
相关电子书
更多
《2021云上架构与运维峰会演讲合集》
立即下载
《零基础CSS入门教程》
立即下载
《零基础HTML入门教程》
立即下载