What is the limit on the number of tables in a query in SQL Server?

A. A. With SQL 6.5 and earlier the limit is 16 - regardless of which "version" of SQL you are running - i.e. Enterprise Edition allows no more than standard. With SQL 7.0 the limit is 256. These figures are hard-coded into the SQL kernel and are arbitrarily chosen by the Microsoft developers - but with good reason. The more tables there are, the longer it takes to optimise a query properly. There has to be a trade-off between the speed of running a query and the speed of optimising it.

It *is* possible to up the limit of 16 in SQL 6.5 by setting traceflag -T105. This is an undocumented and unsupported trace flag, so Microsoft may not support you with any problems on a system that is running this. However, it was allegedly put into the product to allow some of the more complex Peoplesoft queries to run, and so it must have had some testing/QA done on it. It ups the limit to 32.

Normally, if a query needs more than 16 tables then you have a very bad query and/or database design. The best practice would be to break the query down into smaller parts and use temporary tables to hold interim resultsets. This will also make the query more understandable and may even speed it up as the optimiser has more of a chance to choose correct access plans and indices.

Finally the table limit only applies to a single query, so you could always use a UNION statement to join together the results of two separate queries.