• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 353
  • Last Modified:

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_Details_XML.XML_Field_Name IS NULL
            THEN Service_Routing_Mapping_Details.FML_Field_Name_To
            ELSE Service_Routing_Mapping_Details.FML_Field_Name_To + '::' + Service_Routing_Mapping_Details_XML.XML_Field_Name
            END + ','
      FROM dbo.Service_Routing_Mapping_Details
      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)
      RETURN NULL

END
0
BigJ
Asked:
BigJ
1 Solution
 
arbertCommented:
You can't create an Index on a view that uses a function like that.....

Post the view and what are the final results that you're really looking for???
0
 
amit1978Commented:
creating a custer index is posible in view
but only on thus field on which parent table has an index.

Amit Jain
0
 
BigJAuthor Commented:
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.
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now