Error: The view contains a convert that is imprecise or non-deterministic. Prevents indexing view.

  • I'd like to be able to index a view having a WHERE clause on date. 

    Index DDL

    USE [DistributionCenterFeed]
    GO
    CREATE UNIQUE CLUSTERED INDEX vidx_DEVICE_RECEIVED_DATE
    ON [dbo].[AIZ_Events_201804onward] (DEVICE_RECEIVED_DATE);

    But whenever I try to create a clustered unique index, I get an error about determinism.  With underlying view I experimented making the WHERE filter deterministic but even when I specify a specific date, errors persist.

    SELECT <COLUMNS) WITH SCHEMABINDING
    FROM TABLE  WHERE DEVICE_RECEIVED_DATE = CAST('2018-04-01 06:05:41.000' AS DATETIME)

    Error: Msg 1963, Level 16, State 1, Line 3 Cannot create index on view "DistributionCenterFeed.dbo.AIZ_Events_201804onward". The view contains a convert that is imprecise or non-deterministic.

    alternately, on underlying view I tried:

    SELECT <COLUMNS) WITH SCHEMABINDING
    FROM TABLE  WHERE DEVICE_RECEIVED_DATE = '2018-04-01 06:05:41.000'

    Error: Cannot create index on view 'DistributionCenterFeed.dbo.AIZ_Events_201804onward' because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value.

    I'd actually like to be able to materialize this view having filter:
    WHERE

    DEVICE_RECEIVED_DATE > getdate() - 180

    --Quote me

  • polkadot - Monday, January 14, 2019 8:23 PM

    I'd like to be able to index a view having a WHERE clause on date. 

    Index DDL

    USE [DistributionCenterFeed]
    GO
    CREATE UNIQUE CLUSTERED INDEX vidx_DEVICE_RECEIVED_DATE
    ON [dbo].[AIZ_Events_201804onward] (DEVICE_RECEIVED_DATE);

    But whenever I try to create a clustered unique index, I get an error about determinism.  With underlying view I experimented making the WHERE filter deterministic but even when I specify a specific date, errors persist.

    SELECT <COLUMNS) WITH SCHEMABINDING
    FROM TABLE  WHERE DEVICE_RECEIVED_DATE = CAST('2018-04-01 06:05:41.000' AS DATETIME)

    Error: Msg 1963, Level 16, State 1, Line 3 Cannot create index on view "DistributionCenterFeed.dbo.AIZ_Events_201804onward". The view contains a convert that is imprecise or non-deterministic.

    alternately, on underlying view I tried:

    SELECT <COLUMNS) WITH SCHEMABINDING
    FROM TABLE  WHERE DEVICE_RECEIVED_DATE = '2018-04-01 06:05:41.000'

    Error: Cannot create index on view 'DistributionCenterFeed.dbo.AIZ_Events_201804onward' because the view uses an implicit conversion from string to datetime or smalldatetime. Use an explicit CONVERT with a deterministic style value.

    I'd actually like to be able to materialize this view having filter:
    WHERE

    DEVICE_RECEIVED_DATE > getdate() - 180

    Have a look at this article on MS Docs, Deterministic and Nondeterministic Functions
    😎

  • Hi @Eirikur Eiriksson, I'm familiar with that post.  Bascially it says that everytime that getdate() runs the value will be slightly different so getdate() is non deterministic and can't have non deterministic function in materialized view.

    But, as you can see i get this error even when I specify a specific date.  So, that's pretty deterministic.  Why the error?

    --Quote me

  • polkadot - Tuesday, January 15, 2019 1:21 AM

    Hi @Eirikur Eiriksson, I'm familiar with that post.  Bascially it says that everytime that getdate() runs the value will be slightly different so getdate() is non deterministic and can't have non deterministic function in materialized view.

    But, as you can see i get this error even when I specify a specific date.  So, that's pretty deterministic.  Why the error?

    Use Use CONVERT(DATETIME,'2018-04-01 06:05:41.000',121) instead of CAST('2018-04-01 06:05:41.000' AS DATETIME) instead of instead of CAST('2018-04-01 06:05:41.000' AS DATETIME)
    😎
    From MS Docs:
    CAST    Deterministic unless used with datetime, smalldatetime, or sql_variant.

    CONVERT    Deterministic unless one of these conditions exists:
    Source type is sql_variant.
    Target type is sql_variant and its source type is nondeterministic.
    Source or target type is datetime or smalldatetime, the other source or target type is a character string, and a nondeterministic style is specified. To be deterministic, the style parameter must be a constant. Additionally, styles less than or equal to 100 are nondeterministic, except for styles 20 and 21. Styles greater than 100 are deterministic, except for styles 106, 107, 109 and 113.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply