From comments to one of my previous posts: SQL Server: deleting with self-referential FOREIGN KEY
In my case the query goes to infinite loop.
If I use
OPTION (MAXRECURSION 365)or
OPTION (MAXRECURSION 3267), it fails when reaching highest recursion value.
If I use 0 (which is infinite) the query runs for an hour with no output.
I understand it is a data related issue which is causing infinite loop, but I cannot control the data being sent to the DB.
Any other way we can handle it?
That's a common situation, and we would sometimes need to clean the mess up (i. e. remove the whole loop).
To do this, we would just need to add a little check condition to the recursive CTE.
We just record the topmost entry (or better say the entry we first use in the loop, as there is obviously no topmost entry), and check if we ever come across it again as we do the recursion.
Once we have it on next iteration, we know it's a loop and it's time to stop.
Here's how we do this:
CREATE TABLE TestComposite ( id INT NOT NULL, siteUrl NVARCHAR(255) NOT NULL, name NVARCHAR(MAX) NOT NULL, parentId INT NULL, PRIMARY KEY (id, siteUrl), FOREIGN KEY (parentId, siteUrl) REFERENCES TestComposite (id, siteUrl) ) GO INSERT INTO TestComposite VALUES (1, 'site1', 'name1', NULL), (2, 'site2', 'name2', 4), (3, 'site2', 'name3', 2), (4, 'site2', 'name4', 3), (5, 'site5', 'name5', NULL), (6, 'site5', 'name6', 5) GO WITH q (start, id, siteUrl) AS ( SELECT id, id, siteUrl FROM TestComposite WHERE id = 2 AND siteUrl = 'site2' UNION ALL SELECT start, tc.id, tc.siteUrl FROM q JOIN TestComposite tc ON tc.parentID = q.id AND tc.siteUrl = q.siteUrl WHERE tc.id <> q.start ) DELETE FROM TestComposite OUTPUT DELETED.* WHERE EXISTS ( SELECT id, siteUrl INTERSECT SELECT id, siteUrl FROM q )
We had a loop on 2 -> 3 -> 4 which is gone after the query.
See this query on SQL Fiddle.