第十章——维护索引（8）——在计算列中创建索引提高性能

/*
前言：
在理解计算列上的索引之前，先了解计算列的基本知识。计算列由可以使用同一表中的其他列的表达式计算得来。表达式可以是非计算列的列名、常量、函数，也可以是用一个或多个运算符连接的上述元素的任意组合。表达式不能为子查询。
默认情况下，计算列是一个虚拟的列，并且可以在调用时重新计算，直到在CREATE TABLE或者ALTER TABLE 命令中使用PERSISTED。
如果列定义成PERSISTED，会存放计算值，并存放在原始列上更新后的汇总值，不能对计算列进行INSERT、UPDATE。
 
准备工作：
首先要了解是否有需要在计算列上创建索引，计算列在下面情况可以考虑创建索引：
1、如果计算列的数据来源于IMAGE,TEXT和NTEXT数据类型，只能作为非聚集索引的部分列。
2、计算列表达式不能是REAL或者FLOAT数据类型。
3、计算列必须明确。
4、计算列必须具有稳定性。可以使用COLUMNPROPERTY函数的IsDeterministic属性来判断是否稳定。
5、如果函数使用了任何函数，不管是自定义还是系统内置的，那么表和函数的拥有者必须是相同的。
6、不能用于通过聚集函数获得的函数值上的列。
7、需要开启下面的配置：
SET ANSI_NULLS ON  
SET ANSI_PADDING ON  
SET ANSI_WARNINGS ON  
SET ARITHABORT ON  
SET CONCAT_NULL_YIELDS_NULL ON  
SET QUOTED_IDENTIFIER ON  
SET NUMERIC_ROUNDABORT OFF  

*/

SELECT  salesorderid ,  
        salesorderdetailid ,  
        carriertrackingnumber ,  
        orderqty ,  
        productid ,  
        specialofferid ,  
        unitprice  
INTO    salesorderdetaildemo  
FROM    AdventureWorks2012.sales.salesorderdetail  
go  


--2、现在创建一个用于计算列的自定义函数，并添加计算列NetPrice到新表中，这个列通过自定义函数UDFTotalAmount来计算值：
 CREATE FUNCTION dbo.UDFTotalAmount  
    (  
      @TotalPrice NUMERIC(10, 3) ,  
      @Freight TINYINT  
    )  
RETURNS NUMERIC(10, 3)  
    WITH SCHEMABINDING  
AS  
    BEGIN  
        DECLARE @NetPrice NUMERIC(10, 3)  
        SET @NetPrice = @TotalPrice +( @totalprice * @Freight / 100 )  
        RETURN @NetPrice  
    END  
GO  
   
--添加计算列： 
ALTER TABLE SalesOrderDetailDemo ADD [NetPrice] AS  
dbo.UDFTotalAmount(OrderQty*UnitPrice,5)  
GO  



--3、现在在表上创建一个聚集索引。使得表不会再是堆表，然后按照前面说的，修改相关的SET选项，然后开启STATISTICS，记住目前没有创建任何索引在计算列上：

 --创建聚集索引 
CREATE CLUSTERED INDEX idx_SalesOrderID_SalesOrderDetailID_SalesOrderDetailDemo ON  
SalesOrderDetailDemo(SalesOrderID,SalesOrderDetailID)  
GO  
   
----开启统计数据 
--SET STATISTICS IO ON  
--SET STATISTICS TIME ON  
--GO  
   
----执行查询 
--SELECT  *  
--FROM    SalesOrderDetailDemo  
--WHERE   NetPrice > 5000  
--GO  

--4、在创建索引到计算列前，先检查是否符合创建条件：
SELECT  COLUMNPROPERTY(OBJECT_ID('SalesOrderDetailDemo'), 'NetPrice',  
                       'IsIndexable') AS 'Indexable?' ,  
        COLUMNPROPERTY(OBJECT_ID('SalesOrderDetailDemo'), 'NetPrice',  
                       'IsDeterministic') AS 'Deterministic?' ,  
        OBJECTPROPERTY(OBJECT_ID('UDFTotalAmount'), 'IsDeterministic') ,  
        'UDFDeterministic?' ,  
        COLUMNPROPERTY(OBJECT_ID('SalesOrderDetailDemo'), 'NetPrice',  
                       'IsPrecise') AS 'Precise?'  


--5、现在在计算列上创建索引，如果你前面说的条件都满足，那么可以创建了：

CREATE INDEX idx_SalesOrderDetailDemo_NetPrice  
ON SalesOrderDetailDemo(NetPrice)  
GO  
/*
分析：
        在计算列创建一个索引，存储键值到叶子节点并在SELECT的时候利用索引的统计信息，在大部分的情况下是工作得很好的。但是也有很多情况下不能用计算列。
        在统计数据上，可以看到SQLServer Parse和Compile 时间还有SQLServer执行时间。如果数据量很大，那么创建了索引在计算列上的效能提高将会很明显。
*/