January 14, 2019 at 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
--Quote me
January 15, 2019 at 1:08 am
polkadot - Monday, January 14, 2019 8:23 PMI'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:
WHEREDEVICE_RECEIVED_DATE > getdate() - 180
Have a look at this article on MS Docs, Deterministic and Nondeterministic Functions
😎
January 15, 2019 at 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?
--Quote me
January 15, 2019 at 1:32 am
polkadot - Tuesday, January 15, 2019 1:21 AMHi @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