BigJ
asked on
Unable to create Clustered Index in View due to NONDETERMINISTIC User Defined Function
(SQL Server 2000)
I am trying to create a Clustered Index in a View, which contains a field that references a User Defined Functions. I get an error that says "Index on View 'XXXXX' could not be created because the view uses a NONDETERMINISTIC User-Defined Function"
The function is passed a parameter that is a foreign key field for a couple of tables. Basically the function looks up all the records in a certain field, and concatenates them into one string.
The function is:
CREATE FUNCTION udf_MAP_To
(@MappingID int)
RETURNS varchar(2000)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @SQL varchar(2000)
SET @SQL = ''
SELECT @SQL = @SQL +
CASE WHEN Service_Routing_Mapping_De tails_XML. XML_Field_ Name IS NULL
THEN Service_Routing_Mapping_De tails.FML_ Field_Name _To
ELSE Service_Routing_Mapping_De tails.FML_ Field_Name _To + '::' + Service_Routing_Mapping_De tails_XML. XML_Field_ Name
END + ','
FROM dbo.Service_Routing_Mappin g_Details
LEFT JOIN dbo.Service_Routing_Mappin g_Details_ XML
ON Service_Routing_Mapping_De tails.ID = Service_Routing_Mapping_De tails_XML. XMLMapping ID
AND Service_Routing_Mapping_De tails_XML. Direction = 'TO'
WHERE Service_Routing_Mapping_De tails.Mapp ingid = @MappingID
GROUP BY Service_Routing_Mapping_De tails.FML_ Field_Name _To, Service_Routing_Mapping_De tails_XML. XML_Field_ Name
IF LEN(@SQL) > 2
RETURN LEFT(@SQL,LEN(@SQL) -1)
RETURN NULL
END
I am trying to create a Clustered Index in a View, which contains a field that references a User Defined Functions. I get an error that says "Index on View 'XXXXX' could not be created because the view uses a NONDETERMINISTIC User-Defined Function"
The function is passed a parameter that is a foreign key field for a couple of tables. Basically the function looks up all the records in a certain field, and concatenates them into one string.
The function is:
CREATE FUNCTION udf_MAP_To
(@MappingID int)
RETURNS varchar(2000)
WITH SCHEMABINDING
AS
BEGIN
DECLARE @SQL varchar(2000)
SET @SQL = ''
SELECT @SQL = @SQL +
CASE WHEN Service_Routing_Mapping_De
THEN Service_Routing_Mapping_De
ELSE Service_Routing_Mapping_De
END + ','
FROM dbo.Service_Routing_Mappin
LEFT JOIN dbo.Service_Routing_Mappin
ON Service_Routing_Mapping_De
AND Service_Routing_Mapping_De
WHERE Service_Routing_Mapping_De
GROUP BY Service_Routing_Mapping_De
IF LEN(@SQL) > 2
RETURN LEFT(@SQL,LEN(@SQL) -1)
RETURN NULL
END
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Amit, I am trying to create an index on a field that, in the underlying table, has a clustered index. I believe arbert may be correct.
Putting it simply, the view queries a parent table which has a child table. For each record in the parent, the user defined function loops through all corresponding records in the child, and concatenates all the values in a particular field into ONE STRING.
For instance:
Parent Table:
========
PK*
--------------
1
2
3
Child Table
=======
FK textField
-----------------
1 this
1 is
1 the result
2 blah
2 blah
3 hello
3 world
View Reult:
=======
ID textField
-----------------
1 this is the result
2 blah blah
3 hello world
*The PK Key field of the parent table is a clustered index.
Putting it simply, the view queries a parent table which has a child table. For each record in the parent, the user defined function loops through all corresponding records in the child, and concatenates all the values in a particular field into ONE STRING.
For instance:
Parent Table:
========
PK*
--------------
1
2
3
Child Table
=======
FK textField
-----------------
1 this
1 is
1 the result
2 blah
2 blah
3 hello
3 world
View Reult:
=======
ID textField
-----------------
1 this is the result
2 blah blah
3 hello world
*The PK Key field of the parent table is a clustered index.
but only on thus field on which parent table has an index.
Amit Jain