OUTER JOIN table limit?

OUTER JOIN table limit?

• kevin delafield

  That article makes several incorrect assumptions and looks like the authors are not well informed about SQL Server. For example, the support for recognizing transitive predicates has been in the product since SQL Server 7.0. See link below:

  http://www.microsoft.com/technet/prodtechnol/sql/70/reskit/part9/sqc13.mspx mfr=true

  I didn't go through all the chapters but the first page itself has several errors and doesn't apply to SQL Server. Performance of joins involving large number of tables can be a problem depending on the resources and the query plan. This has also been improved considerably in the product for every release starting from SQL 6x. If you are looking for querying tips for SQL Server, you may want to look at some of the new Inside SQL Server 2005 series for example.

  
  
  

• OsirisTerje

  as far as sql server is concerned there is no limitation.

  it could have been a limitation from data access tier

  e.g. dataset

  
  
  

• msatmti

  Hello:

  I was pointed to this "Three-Way Joins and Beyond" in SQL Pperformance Tuning by Peter Gulutzan and Trudy Pelzer.

  Can I get some explaination for this statement: "You can expect the DBMS optimizer to start going wrong if five or more joins are in the query (until recently Microsoft's admitted limit was four). "

  Is there anything I miss here

  This book was published on September 10, 2002.

  Thank you.

  
  

• Saila

  Hi Umachandar:

  Thank you for answering this question. I used multiple outer joins to extract a dataset from my database. That table limit statement could pose a serious problem to my query if it were true for SQL Server 2000 or 2005.

  
  

• webdad3

  There is no limit in the ANSI SQL standard or SQL Server. In fact, the ANSI SQL standard doesn't talk about such limits anywhere. They provide specifications on the syntax and how it should work etc. Note that a particular database implementation can have limits imposed. SQL Server 2005 for example has a maximum limit of 256 table references in a SELECT statement. So you can end up in some situation where the query optimizer cannot produce a plan due to insufficient resources in the system if the query is too complex and contains large number of table references. See below example for SQL Server 2005 which allows more than 4 outer joins:

   

  with t(i)
  as (
  select 1
  )
  select *
  from t as t1
  left join t as t2
  left join t as t3
  left join t as t4
  left join t as t5
  left join t as t6
  left join t as t7
  left join t as t8
  left join t as t9
  left join t as t10
  on t10.i = t9.i
  on t9.i = t8.i
  on t8.i = t7.i
  on t7.i = t6.i
  on t6.i = t5.i
  on t5.i = t4.i
  on t4.i = t3.i
  on t3.i = t2.i
  on t2.i = t1.i

  
  
  
• OUTER JOIN table limit?