May 5, 2008 at 5:07 am
Hello,
This is sathish, how many tables we can join in teh single select statement.
Regards,
sathish.
May 5, 2008 at 5:43 am
How many have you got?
Seriously though, I've seen performance begin to degrade after 50 or so joins, mostly due to the time it takes to compile the query, not the execution time. If you've got fewer than 20 joins, just be sure to test the performance so that you're getting good index usage, etc., and don't worry about it.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 5, 2008 at 5:48 am
sathishmssql (5/5/2008)
Hello,This is sathish, how many tables we can join in teh single select statement.
Regards,
sathish.
Why you require to join so many tables? Is it for your knowledge? If not then recheck your database design. I don't think a good design will require more than 10-12 tables for joing. 🙂
May 5, 2008 at 5:57 am
[font="Verdana"]
Grant Fritchey (5/5/2008)
How many have you got?Seriously though, I've seen performance begin to degrade after 50 or so joins, mostly due to the time it takes to compile the query, not the execution time. If you've got fewer than 20 joins, just be sure to test the performance so that you're getting good index usage, etc., and don't worry about it.
Grant, you are right, however there are limitations for table, i.e. Indexs per table, Number of columns in one index etc, How many tables can be joined in a single query? I am just curios to know..;)
Mahesh
[/font]
MH-09-AM-8694
May 5, 2008 at 6:09 am
The max appears to be 256. I suspect you'll run into other problems long before you hit that limit.
As to the post that 10-12 is a max, I just don't agree. You join what you need to join to return the right result set. You normalize your data appropriately. If it means 3 joins, great. If it means 22, OK. Make sure the 3 or the 22 are optimized and using a good set of indexes. Performance can fly at 22 or stink at 3 and, of course, vice-versa.
----------------------------------------------------The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood... Theodore RooseveltThe Scary DBAAuthor of: SQL Server 2017 Query Performance Tuning, 5th Edition and SQL Server Execution Plans, 3rd EditionProduct Evangelist for Red Gate Software
May 5, 2008 at 8:11 am
Couldn't find anything in BOL, but out of curiosity, I tested it. Max is 256.
When I tried:
create table #T (ID int primary key)
insert into #t (id) select 1
select 1
from #t t1,
#t t2,
#t t3,
.... (fill in the blank with the rest of the table numbers)
#t t1000
I got:
Msg 106, Level 15, State 1, Line 2
Too many table names in the query. The maximum allowable is 256.
As an aside, I got the From table list by running:
select '#t t' + cast(number as varchar(10)) + ','
from dbo.numbers
where number between 2 and 1000
Then just copy-and-pasted the results into the first query.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
May 6, 2008 at 9:57 am
The maximum number of tables in a select is 256.
SQL 2000 http://msdn.microsoft.com/en-us/library/aa933149(SQL.80).aspx
SQL 2005 http://msdn.microsoft.com/en-us/library/ms143432.aspx
Those are the maximum capacity specs for SQL 2000 and 2005, I haven't needed to find the link for 2008 yet.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply