SQL Server里的特殊索引(计算索引和视图索引)

除了常见的索引,SQL Server 还提供了基于计算列和视图的索引,这两种索引的一个共同之处就是它们都定义在虚拟的数据库对象上.

1.Prerequisites

对于给定的函数和输入值,为了保证结果的确定性,必须满足如下条件:

(1)SET Options

SET CONCAT_NULL_YIELDS_NULL ON
SET QUOTED_IDENTIFIER ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF

check当前值

SELECT SessionProperty('NUMERIC_ROUNDABORT')

SELECT quoted_identifier, arithabort, ansi_warnings,
ansi_padding, ansi_nulls, concat_null_yields_null
FROM sys.dm_exec_sessions
WHERE session_id = @@spid

(2) 允许使用的函数

只支持确定性函数,对于确定的输入,输出必须保持不变

可以使用下面的语句判定一个用户定义的函数是否是确定性函数:

SELECT OBJECTPROPERTY(object_id(''), 'IsDeterministic')

对于包含float或者real类型的情况,这些real或者float类型的字段必须是持久化的.

(3) Schema Binding

在创建试图时必须指定WITH SCHEMABINDING


2. 带索引的计算列

(1) 带索引的计算列是要占用物理存储空间的

Note: Persisted Columns: 让不带索引的计算列持久化
ALTER TABLE [Order Details] ADD Final AS (Quantity * UnitPrice) - Discount * (Quantity * UnitPrice) PERSISTED;

3. Indexed Views

(1) 某些方面象oracle的materialized view,支持查询重写

(2) 在一个view上定义的第一个index必须是clustered index

(3) 对view的限制,一下函数不能使用:

·TOP
·text, ntext, or image columns
·DISTINCT
·MIN, MAX, COUNT(*), COUNT(), STDEV, VARIANCE, AVG
·SUM on a nullable expression
·A derived table
·The ROWSET function
·Another view (you can reference only base tables)
·UNION
·Subqueries, OUTER joins, or self-joins
·Full-text predicates (CONTAINS, FREETEXT)
·COMPUTE, COMPUTE BY
·ORDER BY

(4) creating indexed views

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;

CREATE UNIQUE CLUSTERED INDEX VDiscount_Idx ON Vdiscount1 (ProductID);

(5) 数据更新

a. indexed views是自动刷新的

b. 对于indexed view的更新将导致相关的数据表也被更新

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/100682/viewspace-1021792/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/100682/viewspace-1021792/