How many tables can be used in a single SELECT statement ? Have you tested that ?

 Posted by PandianS on 4/26/2011 | Category: Sql Server Interview questions | Views: 5213 | Points: 40
Answer:

It depends on Version

SQL Server 2005:
Maximum tables can be 256

SQL Server 2008:
Depends on resource availability

SQL Server 2008 R2:
Depends on resource availability

You can confirm by using script given below

/*Creating 300 Tables for testing*/
Use [Master]

Go
Declare @I Int, @Script Varchar(500)
Select @I = 1

While (@I <=300)
Begin
Select @Script = 'Create Table Table' + CAST(@I as varchar) + '(Id Int)'
Exec(@Script)
Select @I = @I + 1
End
Go
/*Using all the tables in SELECT statement*/
Use [Master]

Go
Declare @I Int, @Script Varchar(Max)
Select @I = 1
Select @Script = 'Select A1.* From '

While (@I <=299)
Begin
if (@I >1)
Select @Script = @Script + ' Join Table' + CAST(@I+1 as varchar) + ' A' + CAST(@I+1 as varchar) + ' On (' + 'A' + CAST(@I+1 as varchar) + '.Id=' + ' A' + CAST(@I as varchar) + '.Id)'
else
Select @Script = @Script + 'Table' + CAST(@I as varchar) + ' A' + CAST(@I as varchar) + ' Join Table' + CAST(@I +1 as varchar) + ' A' + CAST(@I +1 as varchar) + ' On (' + 'A' + CAST(@I as varchar) + '.Id=' + ' A' + CAST(@I+1 as varchar) + '.Id)'

Select @I = @I + 1
End

EXEC(@Script)
Go
The script will confirm the limitation of using tables in a SELECT statement.


Asked In: Many Interviews | Alert Moderator 

Comments or Responses

Posted by: Chvrsri on: 4/27/2011 | Points: 10
Hi PandianS,

Interesting question. Thank you for sharing !!!
Posted by: Lokesh76 on: 4/27/2011 | Points: 10
Excellent question. Thanks
Posted by: Kishork80 on: 4/27/2011 | Points: 10
Excellent finding !!!.... but in Practicality we do not use more than 5-10 tables in JOIN.
Posted by: PandianS on: 4/27/2011 | Points: 10
Thanks for your comments...

@Kishore:
Yes. you are correct.
- A single SELECT statement can have 4096 columns. The reason is according to best practice/performance point of view, Only 4 table(s) are recommended to use in a single SELECT statement. (4 * 1024 = 4096).
- In our real practice, we will not use these much of table(s) in a single statement :)

Cheers
www.sqlserverbuddy.blogspot.com
Posted by: Susanthampy on: 4/27/2011 | Points: 10
Brilliant questing. Thanks for sharing this question with us.
Posted by: Nithadeepak on: 5/2/2011 | Points: 10
Good Question.
Posted by: Avdesh on: 7/4/2011 | Points: 10
very good post

Login to post response