Closed loops in database model, does it affect the performance? I have heard that having loops in the database relational model should be avoided. From what I can think of, it is not a good practice, because there will be more than one different pathes to get the same results and might lead to inconsistent records in the query result. 

Is that true? Also I'm wondering if it affects the query plans and SQL Server performance. 

What do you think? What are the other troubles it might cause?
I have heard that having loops in the database relational model should be avoided.


To me, a table structure (>1 table) where the foreign keys form circular dependencies (traversing foreign keys starting from one table, it's possible to end up back at the same table) is a red flag indicating a likely problem with the table/system design.

I'm not going to say this is never correct, but I have yet to see an example where this kind of thing was used correctly, or could not be reasonably redesigned to avoid the use of a circular dependency.

(It's the same type of issue in other areas too, as I pointed out here.)


  Table 'Answers' is linked to 'QuestionaireResultsets',but 'QuestionaireResultsets' is also linked to 'Questionnaires', which forms a loop.


Those tables are related, yes, but the relationships probably should be of the form:

Questionnaires 1..* QuestionaireResultsets  

QuestionaireResultsets *..* Answers, with a join table in between

These changes result in a structure that does not contain a circular dependency.

Remember that foreign key relationships have a direction. This is very important. Tables can be related such that a schema diagram looks like a circle; it's only a problem when all the segments of the circle point in the same direction.


  I'm wondering if it affects the query plans and SQL Server performance.


If you ask SQL Server to perform a task using recursion instead of using sets or iteration, it's possible that performance could suffer; however, the reverse may be true as well. I'm not sure we can have a meaningful discussion about performance in this case without a specific example.