Menu
What is the maximum number of joins allowed in SQL Server 2008?
699
Theoretically, there is no upper limit on the number of tables that can be joined using a SELECT statement. (One join condition always combines two tables!) However, the Database Engine has an implementation restriction: the maximum number of tables that can be joined in a SELECT statement is 64.
The more joins you have the exponentially larger the space of possible query plans will be and you may well get very sub optimal plans. As per Microsoft Guidelines, more than five tables are not allowed to join in SQL Server.
There are four main types of JOINs in SQL: INNER JOIN, OUTER JOIN, CROSS JOIN, and SELF JOIN.
With an inner join, the maximum number of rows possible is always the number of rows in the larger table—in this case, eight.
The other answers already give the direct answer to your question
Limited only by available resources
However even if SQL Server successfully compiles a plan for your query that doesn't mean that you should. The more joins you have the exponentially larger the space of possible query plans will be and you may well get very sub optimal plans.
For a query with 12 joins the number of possible join orders is 28,158,588,057,600. Additionally each join may be of three possible algorithms (hash, nested loops, merge)
In the book "SQL server 2005 practical troubleshooting" Cesar Galindo-Legaria says
If you are joining over 20 tables, chances are the optimizer is not reviewing the entire search space but relying more on heuristics .... we have seen applications that run regular queries dealing with over 100 tables. While it is possible to run such very large queries, you really are stretching the system in these cases and should be very careful going this far
126
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
