Unable to create Clustered Index in View due to NONDETERMINISTIC User Defined Function
Posted on 2004-08-09
(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
DECLARE @SQL varchar(2000)
SET @SQL = ''
SELECT @SQL = @SQL +
CASE WHEN Service_Routing_Mapping_Details_XML.XML_Field_Name IS NULL
ELSE Service_Routing_Mapping_Details.FML_Field_Name_To + '::' + Service_Routing_Mapping_Details_XML.XML_Field_Name
END + ','
LEFT JOIN dbo.Service_Routing_Mapping_Details_XML
ON Service_Routing_Mapping_Details.ID = Service_Routing_Mapping_Details_XML.XMLMappingID
AND Service_Routing_Mapping_Details_XML.Direction = 'TO'
WHERE Service_Routing_Mapping_Details.Mappingid = @MappingID
GROUP BY Service_Routing_Mapping_Details.FML_Field_Name_To, Service_Routing_Mapping_Details_XML.XML_Field_Name
IF LEN(@SQL) > 2
RETURN LEFT(@SQL,LEN(@SQL) -1)