SQL Server ISDATE Indexed

Question

I have an index view where I basically need to do this

SELECT ... CASE WHEN ISDATE(ColumnName) = 1 THEN CONVERT(datetime, ColumnName, 103) ELSE NULL END AS ViewColumn .... 

Trying to create index returns:

Unable to create index in view '....'. The 'isdate' function gives non-deterministic results. Use a deterministic function system or modify a user-defined function to return deterministic results.

MSDN says

ISDATE is deterministic only if you use it with the CONVERT function,
if the CONVERT style parameter is specified and the style is not 0, 100, 9, or 109.

here http://msdn.microsoft.com/en-us/library/ms187347.aspx .

But I do not know what that means. As far as I can tell, I use it with the CONVERT function ....

How to get around this?

Answer 1

This should be, if at all:

 SELECT ... CASE WHEN ISDATE(ColumnName) = 1 THEN CONVERT(datetime, ColumnName, 103) ELSE NULL END .... 

but you are not using ISDATE WITH CONVERT since there is no expression like

 ISDATE(CONVERT(varchar,ColumnName,112)) 

without a nested conversion, the return value depends on things such as language settings, therefore this is non-deterministic behavior. Without "external" knowledge, it is impossible to predict the result obtained on the basis of only one input.

Answer 2

Link What are the requirements for indexed views? There are several requirements to consider when using indexed views.

  1. View definition must always return the same results from the same underlying data. 2. Views cannot use non-deterministic functions. 3. The first index on a View must be a clustered, UNIQUE index. 4. If you use Group By, you must include the new COUNT_BIG(*) in the select list. 5. View definition cannot contain the following (A) TOP (B) Text, ntext or image columns (C)DISTINCT (d)MIN, MAX, COUNT, STDEV, VARIANCE, AVG (E)SUM on a nullable expression (F)A derived table (G)Rowset function (H)Another view (I)UNION (J)Subqueries, outer joins, self joins (K)Full-text predicates like CONTAIN or FREETEXT (L)COMPUTE or COMPUTE BY (M)Cannot include order by in view definition