Lesson Learned #507:Error 4429 - View or function 'XYZ' contains a self-reference.

This last week, we worked on a service request where our customer got the following error message: "Msg 4429, Level 16, State 1, Procedure Viewd, Line 2 [batch Start Line 9]

View or function 'Viewa' contains a self-reference. Views or functions cannot reference themselves directly or indirectly. Msg 4413, Level 16, State 1, Line 10 Could not use view or function 'viewB' because of binding errors.". Following I would like to share the lessons learned about this error.

This error means that a view or function has been defined in such a way that it references itself, either directly or indirectly. This can lead to infinite loops and performance issues, which SQL Server prevents by issuing this error.

For example, CREATE VIEW ViewA AS SELECT * FROM ViewA or indirectly using the following code.

CREATE VIEW ViewA AS
SELECT * FROM ViewB;

CREATE VIEW ViewB AS
SELECT * FROM ViewA;

In some situations, we can also observe a view (ViewA) that calls a function, and the definition of that function calls the same view (ViewA). Use these DMVs we could check for indirect references that might be causing a circular dependency.

SELECT
   referencing_object_name = o1.name,
   referencing_object_type = o1.type_desc,
   referenced_object_name = o2.name,
   referenced_object_type = o2.type_desc
FROM sys.sql_expression_dependencies sed
JOIN sys.objects o1 ON sed.referencing_id = o1.object_id
JOIN sys.objects o2 ON sed.referenced_id = o2.object_id
WHERE o1.name = 'ViewA' OR o2.name = 'ViewA';

Continue reading...