ITPub博客

首页 > 数据库 > SQL Server > 关于MSSQL中计算列上建索引的探讨

关于MSSQL中计算列上建索引的探讨

原创 SQL Server 作者:sqysl 时间:2009-04-20 19:58:27 0 删除 编辑

今天在群里,有个网友问到有关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

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

请登录后发表评论 登录
全部评论
上世纪90年代初至今,一直专注于数据库应用及研究,擅长数据库优化及设计等。http://blog.itpub.net/8484829;https://blog.csdn.net/tuning_optmization;https://www.cnblogs.com/lhdz_bj/

注册时间:2008-06-27

  • 博文量
    356
  • 访问量
    560493