關於MSSQL中計算列上建索引的探討

今天在群裡，有個網友問到有關MSSQL建函式索引的問題，查了下資料，比較複雜，而且不怎麼好用，起碼沒有ORACLE中的函式索引那麼好用，現將實驗過程寫在下面：
首先，建立表：
use test1
go
create table test1(aa varchar(10),sub_aa as substring(aa,2,2))
go
然後，測試sub_aa列的可索引值，也就是說是否支援在該計算列上建立索引：
use test1
go
SELECT case COLUMNPROPERTY( OBJECT_ID('dbo.test1'),'sun_aa','isindexable')
       when 0 then 'no'
       when 1 then 'yes'
       end
AS 'Column sun_aa isindexable';
go
然後，測試sub_aa列的確定性：
use test1
go
SELECT case COLUMNPROPERTY( OBJECT_ID('dbo.test1'),'sub_aa','isdeterministic')
       when 0 then 'no'
       when 1 then 'yes'
       end
AS 'Column sun_aa isdeterministic';
go
然後，測試所用函式的確定性：
SELECT OBJECTPROPERTY(OBJECT_ID('substring'),'isdeterministic')
as 'function substring isdeterministic'
因這裡是系統內建函式，不需要測試，測試返回為FULL。
然後，測試函式的系統和使用者訪問：
select case objectpropertyex(object_id('substring'),'systemdataaccess')
       when 0 then 'no'
       when 1 then 'yse'
       end
這裡因為用的是內建函式SUNSTRING，這裡不用測試，測試返回為NULL。
然後，測試表的計算列的精度
use test1
go
select case columnproperty(object_id('dbo.test1'),'sub_aa','isprecise')
       when 1 then 'yes'
       when 0 then 'no'
       end
返回為yes，可以。
最後，在計算列上建立索引：
use test1
go
create index test1_col_com on test1(sub_aa)
go
由此，可以看出，MSSQL的計算列上的索引還是比較複雜的，而且功能沒ORACLE的函式索引那麼簡便和強大，也就是，在MSSQL上，其實就等於在表中要建立一個列，而這個列的值的來源是一個計算表示式，因此，如果我要想在現有列的函式上建立個索引，就必須再建立一個列，該新建列值的來源是基於原來那個列的計算表示式。
此外，如果函式為使用者自定義函式，那麼建立函式和表時，以下命令具有參考價值：
        USE [AdventureWorks]
　　GO　　-- Create UDF to use in computed column expression
　　CREATE FUNCTION
　　[dbo].[UDF_CalculatePay] ( @basicPay INT, @BonusPercentage TINYINT, @TaxPercentage TINYINT)
　　RETURNS INT
　　 WITH SCHEMABINDING　　AS
　　BEGIN
　　DECLARE @TotalPay INT
　　SET @TotalPay = @basicPay + @basicPay*@bonusPercentage/100 - @basicPay*@taxPercentage/100
　　RETURN @TotalPay
　　END
　　GO
　　IF OBJECT_ID('CCIndexTest', 'U') IS NOT NULL
　　DROP TABLE CCIndexTest
　　GO
　　-- Create table CCIndexTest with two computed columns
　　CREATE TABLE [dbo].[CCIndexTest](
　　[EmpNumb] [INT] NOT NULL,
　　[DOBirth] [DATETIME] NULL,
　　[DORetirement] AS (DATEADD(YEAR,(60),[DOBirth])-(1)) PERSISTED,
　　[BasicPay] [SMALLINT] NULL,
　　[BonusPercentage] [TINYINT] NULL,
　　[TaxPercentage] [TINYINT] NULL,
　　[TotalPay] AS [dbo].[UDF_CalculatePay] ( basicPay, BonusPercentage, TaxPercentage)
　　) ON [PRIMARY]
　　GO