SQL Server ISDATE In Indexed View

Question

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?

score 4 · Accepted Answer · answered Jan 25 '12 at 18:00

4

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.

answered Jan 25 '12 at 18:00

Mithrandir

24,869
6
50
66

so it's not possible to use ISDATE with an indexed view? Is there anything I can do about this? – Jeff Jan 25 '12 at 19:38
2

You could build your own scalar function that is deterministic and use that, if it's worth the bother for you. Do you really need the view to be indexed that badly? – Mithrandir Jan 25 '12 at 19:53

Pankaj · Answer 2 · 2012-01-26T06:09:38.360

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

Where is the index on the view? Your view isn't indexed that will work fine! TRY : CREATE INDEX IDX_SOMETHING ON dbo.[v](ed); — Mithrandir, Jan 25 '12 at 19:49

SQL Server ISDATE In Indexed View

2 Answers2

Linked