What is the maximum number of joins allowed in SQL Server 2008?

Question

What is the maximum number of joins allowed in SQL Server 2008?

Answer 1

For inner join, max 256 tables can be joined. For outer join, max 2 tables can be joined.

Source: classroom training.

Answer 2

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

Answer 3

In SQL Server 2008, the maximum number of tables you can have in a SELECT is limited only by available resources (source).

In SQL Server 2005, there was a 256 table limit for a single SELECT (source).

Though, if you're getting up to those sorts of numbers, then I'd be getting a bit concerned tbh!

Answer 4

The limitations for SQL Server are listed here

The number of tables per query is only limited by the amount of available resources.