关于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