Functional Index in SQL SERVER 2005

Question

Suppose you have a BEER_HERE table with two columns:

BEER_CODE VARCHAR(50) BEER_DATE DATETIME 

Suppose also that you have 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?

Thank you in advance for your kind help.

Answer 1

You will need to add a computed column

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

You can then index it, as you would expect.

However, your function must be deterministic and accurate, as defined in http://msdn.microsoft.com/en-us/library/ms189292(v=sql.90).aspx . Your function must meet these requirements, but you may need to add a function definition using SchemaBinding.

You can also use the view.

 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) 

Material that inserts records into a table, material that reads readings from a view. It depends on BEER_CODE being the primary key.

SQL Server does not have function-based indexes, just like Oracle does.