September 22, 2011 at 2:56 pm
I am trying to create an indexed view that has spatial functions (STIntersects), but I get the error:
Msg 1982, Level 16, State 1, Line 2
Cannot create index on view 'arcgis_spatial.GISadmin.consunit_cons_filter_v' because the view references non-deterministic or imprecise member function 'STIntersects' on CLR type 'Microsoft.SqlServer.Types.SqlGeometry'. Consider removing reference to the function or altering the function to behave in a deterministic way. Do not declare a CLR function that behaves non-deterministically to have IsDeterministic=true, because that can lead to index corruption. See Books Online for details.
Firstly, why is STIntersects non-deterministic and secondly, is there a way around this? Like using geography types instead of geometry? Are there other spatial functions that are deterministic? STRelate for example.
Code I was running.
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Alter view
ALTER VIEW GISadmin.consunit_cons_filter_v
WITH SCHEMABINDING
AS SELECT a.CONSUNIT_NO, b.CONSERVANCY
FROM [GISadmin].[ADMINISTRATIVE_LANDREG_ConservationUnits] a
,[GISadmin].[ADMINISTRATIVE_DOC_CONSERVANCIES] b
WHERE a.geometry1.STIntersects(b.shape) = 1;
GO
--Create index on view
CREATE UNIQUE CLUSTERED INDEX co_cu_v_idx
ON GISadmin.consunit_cons_filter_v (consunit_no, conservancy);
September 22, 2011 at 5:01 pm
bmnelson (9/22/2011)
I am trying to create an indexed view that has spatial functions (STIntersects), but I get the error:Msg 1982, Level 16, State 1, Line 2
Cannot create index on view 'arcgis_spatial.GISadmin.consunit_cons_filter_v' because the view references non-deterministic or imprecise member function 'STIntersects' on CLR type 'Microsoft.SqlServer.Types.SqlGeometry'. Consider removing reference to the function or altering the function to behave in a deterministic way. Do not declare a CLR function that behaves non-deterministically to have IsDeterministic=true, because that can lead to index corruption. See Books Online for details.
Firstly, why is STIntersects non-deterministic and secondly, is there a way around this? Like using geography types instead of geometry? Are there other spatial functions that are deterministic? STRelate for example.
Code I was running.
GO
--Set the options to support indexed views.
SET NUMERIC_ROUNDABORT OFF;
SET ANSI_PADDING, ANSI_WARNINGS, CONCAT_NULL_YIELDS_NULL, ARITHABORT,
QUOTED_IDENTIFIER, ANSI_NULLS ON;
GO
--Alter view
ALTER VIEW GISadmin.consunit_cons_filter_v
WITH SCHEMABINDING
AS SELECT a.CONSUNIT_NO, b.CONSERVANCY
FROM [GISadmin].[ADMINISTRATIVE_LANDREG_ConservationUnits] a
,[GISadmin].[ADMINISTRATIVE_DOC_CONSERVANCIES] b
WHERE a.geometry1.STIntersects(b.shape) = 1;
GO
--Create index on view
CREATE UNIQUE CLUSTERED INDEX co_cu_v_idx
ON GISadmin.consunit_cons_filter_v (consunit_no, conservancy);
My guess is that your function has a select statement in it. If you function did not have any selects in it, but just did some sort of base computation then you could do an indexed view with it.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply