Indexing on calculated fields

I would like to index on a calculated field. I need to index records by
w and want to store the W Number. Below is a test schema that
should work but the index will not create.
drop table calcdate;
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,[basedate],0)
);
create index calcdate_idx ON calcdate(calcsun);
I get the error " 37000(1933)[Microsoft][ODBC SQL Server Driver][SQL
Server]Cannot create index because the key column 'calcsun' is
non-deterministic or imprecise."
The page
http://msdn.microsoft.com/library/d...>
_08_95v7.asp
says that DATEDIFF is deterministic so I cannot see why the above index
will not create.
All help much appreciated.
GJHello, GJ
I was somehow surprised of this behaviour, too. It turns out that
DATEDIFF is indeed deterministic, but of it's parameters was not: 0 as
a datetime is non-deterministic! The page you quoted says that CONVERT
is deterministic with a datetime only when the style parameter is
specified (and it's not 0, 9, 100 or 109). Therefore, this works (and I
think that you will get the same results):
create table calcdate
(
basedate datetime,
calcsun AS datediff(wk,basedate,convert(datetime,'1
9000101',112))
);
create index calcdate_idx ON calcdate(calcsun);
Razvan