SQL Server Performance

Maximum number of Joins

Discussion in 'General Developer Questions' started by AFlowers, Apr 23, 2004.

  1. AFlowers New Member

    Hi,

    What is the maximum number of Joins supported for SQL Server 2000?

    Thanks!

    Alf
  2. x002548 New Member

    256

    You get anywhere near 10 you'll have trouble...

    Used to be 16 in 6.5

    I once had 3 statements in a sproc, each with 16 left joins...

    That didn't do too bad, but it was to build a huge denormalized row in 3 passes...







    Brett

    :cool:
  3. bradmcgehee New Member

    The maximum number of columns permitted in a SELECT statement is 4,096, and I presume that this is the upper limit of JOINs in a SELECT statement. I have seen queries with dozens and dozens of JOINs, but generally speaking, I try to keep them to an absolute minimum.

    -----------------------------
    Brad M. McGehee, MVP
    Webmaster
    SQL-Server-Performance.Com
  4. x002548 New Member

    BOL


    quote:
    Arguments
    <table_source>

    Specifies a table or view, both with or without an alias, to use in the Transact-SQL statement. A maximum of 256 tables can be used in the statement. A table variable may be specified as a table source.




    Brett

    :cool:
  5. FrankKalis Moderator

    Although Brett is correct on this with 256 (look at Maximum capacity specification in BOL), be it 256 or 4,096, both numbers are rather theoretical. <br /><br />If someone is about to reach here any limits, I would seriously consider reviewing the data model. And the more tables you join, the more coffee you need when waiting for the results on large tables [<img src='/community/emoticons/emotion-2.gif' alt=':D' />]<br /><br />Frank<br /<a target="_blank" href=http://www.insidesql.de>http://www.insidesql.de</a><br /<a target="_blank" href=http://www.familienzirkus.de>http://www.familienzirkus.de</a>
  6. AFlowers New Member

    Thanks to all for your comments. This pretty much tells me what I need to know! - Alf
  7. Raulie New Member

    One way you can circumvent this problem is for example if you want to create a 10 table join you can join five tables in each of two queries, output the results to Temp tables, run a third query that joins the two temp tables to get the output. Remember eliminate the temp tables immediately after the third join query is run to reduce over head on tempDB.
  8. gaurav_bindlish New Member

    Useful suggestion by Lazy_DBA but then using temp tables has its own disadvantages.

    Gaurav
    Moderator
    Man thrives, oddly enough, only in the presence of a challenging environment- L. Ron Hubbard

    The views expressed here are those of the author and no one else. There are no warranties as to the reliability or accuracy of anything presented here.
  9. Raulie New Member

    Very true, I try to stay away from temp tables unless I absolutely must use them.

Share This Page