Please suppose you have a table called BEER_HERE, with two columns:
BEER_CODE VARCHAR(50)
BEER_DATE DATETIME
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?
Thank you in advance for your kind help.
You would 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'd expect.
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.
You might also be able to use a 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)
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 doesn't have function based indexes the same way Oracle does.
