SQL SERVER 2005 中基于函数的索引 [英] Function Based Index in SQL SERVER 2005

问题描述

请假设您有一个名为 BEER_HERE 的表，有两列:

Please suppose you have a table called BEER_HERE, with two columns:

BEER_CODE VARCHAR(50)
BEER_DATE DATETIME

请假设您还有一个名为 dbo.TRUNCATE_DATE 的函数，其工作方式如下:

Please suppose also you have the availability of a function called dbo.TRUNCATE_DATE which works as follows:

dbo.TRUNCATE_DATE ('23/12/2012 23:59:57.395') ==> 23/12/2012 00:00:00.000

我想创建一个基于函数的索引:

I would like to create a FUNCTION BASED INDEX on:

(BEER_CODE, dbo.TRUNCATE_DATE(BEER_DATE))

我怎样才能做到这一点?

How could I achieve this?

预先感谢您的帮助.

推荐答案

你需要添加一个计算列

Alter Table BEER_HERE Add Column XBEER_DATE As dbo.TRUNCATE_DATE(BEER_DATE)

然后，您可以按照预期对其进行索引.

You can then index it as you'd expect.

但是，您的函数需要具有确定性和精确性，如 http://msdn.microsoft.com/en-us/library/ms189292(v=sql.90).aspx.您的函数应该满足这些要求，但您可能需要在函数定义中添加 With SchemaBinding.

However, your function needs to be deterministic and precise as defined in http://msdn.microsoft.com/en-us/library/ms189292(v=sql.90).aspx. Your function should meet these requirements, but you might need to add With SchemaBinding to the function definition.

您也许还可以使用视图

Create View V_BEER_HERE As Select BEER_CODE, BEER_DATE, dbo.TRUNCATE_DATE(BEER_DATE) As XBEER_DATE From BEER_HERE
Create Unique Clustered Index PK_V_BEER_HERE On V_BEER_HERE (BEER_CODE)
Create Index I_XBEER_DATE On V_BEER_HERE (XBEER_DATE)

插入到表中的东西，从视图中读取的东西.这取决于 BEER_CODE 作为主键.

Stuff that inserts writes to the table, stuff that reads reads from the view. This depends on BEER_CODE being a primary key.

SQL Server 不像 Oracle 那样具有基于函数的索引.

SQL Server doesn't have function based indexes the same way Oracle does.

这篇关于SQL SERVER 2005 中基于函数的索引的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！