《Microsoft Sql server 2008 Internals》读书笔记第六章Indexes:Internals and Management(6)

《Microsoft Sql server 2008 Internals》读书笔记第六章Indexes:Internals and Management(6)
《Microsoft Sql server 2008 Internals》读书笔记订阅地址：

http://www.cnblogs.com/downmoon/category/230397.html/rss

《Microsoft Sql server 2008 Internals》索引目录：

《Microsoft Sql server 2008 Internal》读书笔记--目录索引

前几篇文章主要介绍了非聚集索引的物理存储结构，今天我们来看看几类特殊的索引：

１、计算列索引和索引视图（Indexes On computered Columns and Indexed View）

２、全文索引(Full-Text Indexes)

３、空间索引(Spatial Indexes)

４、XML索引(XML Indexes)

■ 计算列索引和索引视图

计算列（Computered Column），MSDN定义：计算列由可以使用同一表中的其他列的表达式计算得来。表达式可以是非计算列的列名、常量、函数，也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。http://msdn.microsoft.com/zh-cn/library/ms191250.aspx

视图：MSDN定义：视图可以被看成是虚拟表或存储查询。除非是索引视图，否则视图的数据不会作为非重复对象存储在数据库中。数据库中存储的是 SELECT 语句。SELECT 语句的结果集构成视图所返回的虚拟表。用户可以采用引用表时所使用的方法，在 Transact-SQL 语句中引用视图名称来使用此虚拟表。

http://msdn.microsoft.com/zh-cn/library/ms190706.aspx

索引视图： MSDN定义：索引视图是指结果保留在数据库中并建立了索引以供快速访问的视图。
http://msdn.microsoft.com/zh-cn/library/aa290257%28VS.71%29.aspx

如果没有索引，计算列和视图是纯逻辑的，没有任何物理存储被调用。计算列也不随表数据一起保存，而在每次一行被访问的时候，重新计算值（除非这个计算列用PERSiSTED标记)，一个视图并不保存任何数据，它只基本地保存了一些Select语句，用于在视图被访问时再执行一次。有了这些特殊的索引，SQL Server实际上

将纯逻辑数据实现为一个索引的叶级。

先决条件：

在你在计算列或索引上创建一个索引之前，有一些先决条件。最大的一个是：SQL Server必须确认，对于给定的相同的基表数据，总有相同的值返回（对任何一个计算列或一个视图的一些行）,即计算列和视图是确定的(deterministic)。为确保相同的植生成，这些特殊的索引，有三类先决条件( prerequisites)，第一、一个会话级选项数字必须被设置为特定的值。第二、在能被计算列或视图定义使用的的function有一些约束。第三、仅对索引视图而言，视图基于的表必须符合固定的标准(meet certain criteria）。

SET Options

关于选项的设置，请参阅MSDN:SET NUMERIC_ROUNDABORT

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 语句中未引用 text、ntext 或 image 列，视图中也不能包含这些列。
- 如果视图定义中的 SELECT 语句指定了一个 GROUP BY 子句，则唯一聚集索引的键只能引用在 GROUP BY 子句中指定的列。
- 构成索引键列值的不精确表达式必须引用视图下基表中的存储列。该列可以是常规存储列，也可以是持久化计算列。其他不精确表达式不能作为索引视图的键列的一部分。
共六个选项，其中最重要的是NUMERIC_ROUNDABORT,查看当前会话选项NUMERIC_ROUNDABORT的设置状态

SELECT SESSIONPROPERTY ('NUMERIC_ROUNDABORT');
go

也可以使用sys.dm_exec_sessions DMV来查看

-- To see the "Set Options that Affect Results" for the current spid:
SELECT quoted_identifier
    , arithabort
    , ansi_warnings
    , ansi_padding
    , ansi_nulls
    , concat_null_yields_null
    --, numeric_roundabort -- Not supported - unfortunately.
FROM sys.dm_exec_sessions
WHERE session_id = @@spid;
go

不幸的是，NUMERIC_ROUNDABORT并不包含在sys.dm_exec_sessions DMV结果中，即只能查看当前会话，而不能另一个连接中相应的设置值。

合法的函数(Permissible Functions)

如果每次给一个固定的值，总能返回相同的结果，这个函数是确定的，否则是不确定的。在这几类特殊索引中，函数必须是确定的。即使函数是确定的，由于 Real,float等类型表达式，function的结果可能有不同，因为可能有四舍五入。如果一个计算列被标记为Persisted，那么，有可能歈数据表中存储一个不精确的值。

架构绑定(Schema Binding)

为了创建一个索引视图，基表自身的要求是它底层的对象架构不能改变。SCHEMABINDING 选项和创建索引视图的更多说明，请查看MSDN:
http://msdn.microsoft.com/zh-cn/library/ms191432.aspx

计算列上的索引

SQL Server 2008允许你在结果数据类型是otherwise indexable的、确定的、精确的(包括persisted imprecise)计算列上创建索引。这句不太理解，原文是SQL Server 2008 allow you to build indexes on deterministic,precise(and persisted imprecise) computered columns where the resulting data type is otherwise indexable.

这意味着列的数据类型不能是任何LOB类型(比如text,varchar(max) or XML),这样，一个计算列能作为一个索引键、包含性列、主键或UNIQUE约束(constrait)的一部分，你不能定义外键、约束(check)，或Default constraint在计算列上，计算列总是被认为是可null的，除非你重新封装(enclose)了isnull函数。

当你在一个计算列上创建一个索引时，前面提到的6个Set 选项，必须首先有纠正值设置，以防出错。

看一个选项设置的例子：

代码

CREATE TABLE t1
(
    a   INT,
    b   AS 2*a
);
go

-- Turn off two required session settings:
SET QUOTED_IDENTIFIER OFF;
SET ANSI_NULLs OFF;
go

-- Attempt to create an index on the computed column (b):
CREATE INDEX i1
ON t1 (b);
go

-- Turn quoted_identifier back on:
SET QUOTED_IDENTIFIER ON;
go

-- Attempt to create an index on the computed column (b):
CREATE INDEX i1
ON t1 (b);
go

-- Turn quoted_identifier back on:
SET ANSI_NULLs ON;
go
-- Finally, success!
CREATE INDEX i1
ON t1 (b);
go

再看一个明确列的例子：

代码

-----------------------------------------------------------------------------
-- Deterministic Columns
------------------------------------------------------------------------------

CREATE TABLE t2
(
    a   INT,
    b   DATETIME,
    c   AS DATENAME(MM, b)
);
go

-- Attempt to create an index on a nondeterministic column:
CREATE INDEX i2
ON t2 (c);
go
--出错：Msg 2729, Level 16, State 1, Line 1
--Column 'c' in table 't2' cannot be used in an index or statistics or as a partition key because it is non-deterministic.

出错的原因是：DateName()对应你使用的语言有不同的值。

使用COLUMNPROPERTY函数，结果列中IsDeterministic决定你在计算列或视图上创建索引时该列是否是明确的 (deterministic)。该列为１表明是明确的，否则是不明确的。

代码

-- Check the column property for determinism:
SELECT COLUMNPROPERTY (OBJECT_ID('t2'), 'c', 'IsDeterministic');
go

-- Is the column indexable (but not why - if it's not):
SELECT COLUMNPROPERTY (OBJECT_ID('t2'), 'c', 'IsIndexable');
go

-- How about column a:
SELECT COLUMNPROPERTY (OBJECT_ID('t2'), 'a', 'IsIndexable');
go

另外，MSDN有篇文章也不错，为计算列创建索引
计算列的实现

如果你在一个计算列上培养索引，则该计算列将不再是表中的一个虚拟列，计算列的值存在于表的行中，也就是聚集索引的叶级。更新这些列时也更新表自身。以上面的举例，如果在列a中插入一个值　

10,在数据行中，实际存储了10和 20，如果我们更新列值为15,则第二列自动更新为30

Persisted Column

标记一个计算列为Persisted的能力允许计算好的值到数据表的值中，即使你没有建立索引，也是如此。这种情况下，该列被索引时，将引起底层列 (underlying)的drop和re-create操作，如前所述，这会引起巨大的开销，特别在一个大表(Larget table)里。

这我在一个例子，在NorthWind数据库中：请大家自己研究。

代码

USE Northwind;
go

-----------------------------------------------------------------------------
-- Attempt to index an imprecise column
------------------------------------------------------------------------------

ALTER TABLE [Order Details]
ADD
    Final AS (Quantity * UnitPrice)
                - Discount * (Quantity * UnitPrice);
go

CREATE INDEX OD_Final_Index
ON [Order Details] (Final);
go

-- To check to see if a computed column must be persisted:
SELECT COLUMNPROPERTY (OBJECT_ID ('Order Details'), 'Final', 'IsPrecise');

-- Instead, if you drop the column and recreate it as a PERSISTED
-- computed column, you can then index it.

ALTER TABLE [Order Details]
DROP COLUMN Final;
go

ALTER TABLE [Order Details]
ADD
    Final AS (Quantity * UnitPrice)
                - Discount * (Quantity * UnitPrice) PERSISTED;
go

CREATE INDEX OD_Final_Index
ON [Order Details](Final);
go

你可以在定义分区时使用Persisted columns。一个计算列被用作分区列的时候，必须被显示地定义为PERSISTED。第七章将有更详细的介绍。

索引视图

在SQL Server中索引视图跟物化视图（materialized　View)有点类似。索引视图的最大好处是可以物化一个大表的摘要合计 (materialize summary aggregates of a large table)，例如，考虑到一个customer表包含几百万行美国的顾客，而你想要统计每个州的相关信息。你可以创建一个基于Group by查询的视图，通常的视图是一个空名，保存查询但不保存结果。而当你在视图上创建索引的时候，合计数据被存储在索引的叶级。取代视图的几百万行的是，索引视图的50行而已,每个州一行。你的合计报表查询，能使用索引视图处理而无须查询底层的大表。

在一个视图上必须创建的第一个索引，是一个聚集索引，因为聚集索引在它的叶级包含了所有数据，这个索引实际是作了视图的物化( materialization)。视图数据被物理存储在聚集索引的叶级。

索引视图的附加条件

如要在 SQL Server 2008 中的视图上创建一个索引，相应的视图定义不能包含下列之一：

■ TOP
■ LOB columns

■ DISTINCT

■ MIN,MAX,COUNT(*),COUNT(<expression>),STDEV,CARIANCE,AVG

■ SUM on a nullable expression

■ A derived table

■ The RWOSET function

■ Another VIew(只能引用基表）

■ UNION

■ Subqueries,OUTER joins,or self-joins

■ Full-text predicates(Contains,freetext)

■ORDER BY

这里有两篇介绍索引视图的不错的文章，
通过 SQL Server 2005 索引视图提高性能
 http://www.microsoft.com/china/technet/prodtechnol/sql/2005/ipsql05iv.mspx

设计索引视图

http://msdn.microsoft.com/zh-cn/library/ms187864.aspx

创建索引视图

下面这个例子，请读者自行体会，呵呵。不是邀月懒，而是光看不练，大家会睡着的。

值得注意的是 WITH SCHEMABINDING子句。

请记住：为了创建一个索引视图，必须首先创建一个惟一的聚集索引。

代码

USE AdventureWorks2008;
go

-- First, check to see if the object is indexable:
SELECT OBJECTPROPERTY (OBJECT_ID ('Sales.SalesOrderDetail'), 'IsIndexable');
go

-- Create a view - with SCHEMABINDING:
CREATE VIEW Vdiscount1
    WITH SCHEMABINDING
AS
SELECT SUM (UnitPrice*OrderQty) AS SumPrice
    , SUM (UnitPrice * OrderQty * (1.00 - UnitPriceDiscount)) AS SumDiscountPrice
    , COUNT_BIG (*) AS Count
    , ProductID
FROM Sales.SalesOrderDetail
GROUP BY ProductID;
go

-- Verify that no data exists for this Indexed View:
SELECT si.name AS index_name
    , ps.used_page_count
    , ps.reserved_page_count
    , ps.row_count
FROM sys.dm_db_partition_stats AS ps
    JOIN sys.indexes AS si
        ON ps.[object_id] = si.[object_id]
WHERE ps.[object_id] = OBJECT_ID ('dbo.Vdiscount1');
go

-- Create a UNIQUE CLUSTERED index on the view to materialize
-- the data:
CREATE UNIQUE CLUSTERED INDEX VDiscount_Idx
ON Vdiscount1 (ProductID);
go

-- Check to see if there's data that exists now:
SELECT si.name AS index_name
    , ps.used_page_count
    , ps.reserved_page_count
    , ps.row_count
FROM sys.dm_db_partition_stats AS ps
    JOIN sys.indexes AS si
        ON ps.[object_id] = si.[object_id]
WHERE ps.[object_id] = OBJECT_ID ('dbo.Vdiscount1');
go

-- To verify whether or not a view has an index:
SELECT OBJECTPROPERTY (OBJECT_ID ('Vdiscount1'), 'IsIndexed');
go

-- Compare/contrast the plans of using the base table (with a table-scan)
-- vs. leveraging the pre-computed values of the indexed view
-- Be sure to turn on showplan (Query menu, Include Actual Execution Plan):
SET STATISTICS IO ON;
go

SELECT ProductID
    , total_sales = SUM (UnitPrice * OrderQty)
FROM Sales.SalesOrderDetail WITH (INDEX (0))
GROUP BY ProductID;
--Table 'Worktable'. Scan count 0, logical reads 0, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.
--Table 'SalesOrderDetail'. Scan count 1, logical reads 1240, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

SELECT ProductID
    , total_sales = SUM (UnitPrice * OrderQty)
FROM Sales.SalesOrderDetail
GROUP BY ProductID;
--Table 'Vdiscount1'. Scan count 1, logical reads 4, physical reads 0, read-ahead reads 0, lob logical reads 0, lob physical reads 0, lob read-ahead reads 0.

索引视图的使用

索引视图的最大好处是查询可以不需要引用视图而直接视图上的索引。假定对于前面创建的视图索引Vdiscount1，有如下查询：

SELECT ProductID
    , total_sales = SUM (UnitPrice * OrderQty)
FROM Sales.SalesOrderDetail
GROUP BY ProductID;

看这个例子，查询优化器辨别(为每一个ProductID)预计算所有的UnitPrice * OrderQty值的总和，是否在索引Vdiscount1中可用，并评估使用视图索引处理查询的成本。索引视图很可能被直接使用，而Sales.SalesOrderDetail表实际上可能根本不会被访问。

注意：尽管你可以在SQL Server 2008的任何版本创建视图索引，但要让查询优化器决定是否需要查询原表或是直接读取索引，则必须是SQL Server的三个版本：企业版、开发版或评估版。

建立视图索引并不意味着查询优化器就一定会选择它来优化查询计划，实际上，还是有可能直接读取基表数据。为了确保视图索引被直接使用，你可以在From子句中使用NOEXPAND暗示。后面第八章将继续讨论。

全文索引(Full-Text Indexes)

关于全文索引的详细介绍，请看MSDN:http://msdn.microsoft.com/zh-cn/library/ms189376.aspx

这里邀月补充下：全文索引是倒置的（inverted）、堆叠的(stacked)、压缩过的(Compressed)一类特殊的索引，它存储在数据库的内表(internal table)中，全文索引数据存储在内部表的规则索引行，（还记得前文所述的规则索引吗？呵呵。）但是，行的多数(majority)对于除了全文引擎本身以外的所有对象都是不透明的(opaque),。像工具DBCC PAGE就不能正确地破解打开行的所有字段。全文索引存储的时候与规则索引相同，但它是存储在internal table中，常规查找它们的结构是无效的。我们可以通过sys.internal_tables目录视图来查询：

正如你所见，内部表与规则表（与索引）在系统目录上有不同的根，尽管它们的空间使用同样的方式被跟踪(使用IAM页),它们的结构也与规则索引完全相同。

空间索引(Spatial Indexes)

MSDN有详细的描述参看:http://msdn.microsoft.com/zh-cn/library/bb964712.aspx

XML索引(XML Indexes)

MSDN同样有详细描述，请看：http://msdn.microsoft.com/zh-cn/library/bb500237.aspx

关于索引的简单学习暂且告一段落。下文将关注数据修改时的存储结构。
邀月注：本文版权由邀月和博客园共同所有，转载请注明出处。
助人等于自助! 3w@live.cn
相关阅读:
SQL 查询中 not in的改进，--not exists
REST接口--转摘
 C#中@的用法总结（转）
有感于哈工大matlab被限制使用
 Oracle CURRVAL应用限制
 oracle to_char()函数--数字型到字符型
 如何提交代码到git仓库
 cannot find module 'xxx' 解决办法
 DOM-基本概念及使用
 AJAX-同源策略跨域访问
原文地址：https://www.cnblogs.com/downmoon/p/1677495.html