SQL Server ISDATE In Indexed View

问题:

I have a indexed 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 the index yields:

Cannot create index on view '....'. The function 'isdate' yields nondeterministic results. Use a deterministic system function, or modify the 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 style is not equal to 0, 100, 9, or 109.

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

But I don't know what that means at all. As far as I can tell, I am using it with a CONVERT function....

Any way to work around this?

回答1:

It 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 the nested convert the return value is dependend on things like language settings, hence it's nondeterministic behaviour. Without "external" knowledge, it's not possible to predict the result one is getting, based on the input alone.

回答2:

Reference What are the requirements for Indexed views? There are several requirements that you must take into consideration 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