Monday, March 13, 2006

In SQL Server 2005 Implicit conversion from string to datetime (Ex: in "where" clause "where BirthDay='01/01/1980'") is considered not-deterministic and can't be present in Indexed View definition.
-- Example:
Create view v_test with SCHEMABINDING as
Select c1,c2 from dbo.t_test where c2 = '01/01/1980'
go
Create unique clustered index idx_v_test on v_test(c1)
go
-- Result:Cannot create index on view 'db_test.dbo.v_test' because the view uses an implicit
-- conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a
-- deterministic style value.


-- Solution: You can use explicit conversion specifying style value for example:
Create view v_test with SCHEMABINDING as
Select c1,c2 from dbo.t_test where c2 = convert(datetime,'01/01/1980',120)
go

-- In this case string will be converted to datetime input as yyyy-mm-dd hh:mi:ss (24h).
Create unique clustered index idx_v_test on v_test(c1)
go
--Result:Command(s)
-- completed successfully.

1 comment:

sqlservernotes said...

Thanks indeed. Very helpful.