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