Discussion in 'General Developer Questions' started by AFlowers, Apr 23, 2004.
What is the maximum number of Joins supported for SQL Server 2000?
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...
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
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.
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='' />]<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>
Thanks to all for your comments. This pretty much tells me what I need to know! - Alf
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.
Useful suggestion by Lazy_DBA but then using temp tables has its own disadvantages.
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.
Very true, I try to stay away from temp tables unless I absolutely must use them.
Separate names with a comma.