Maximum # of tables in a T-SQL query

KB article 828269, and its companion 818406, say that that they increase
the allowable number of tables and views references in a T-SQL query
from 256 to 260.
Is this for real, or am I missing something here? This hotfix touches a
whole slew of DDLs and other parts of SQL, and only gives you the
ability to use 4 additional tables in a query?
The article says this:
"The maximum number of tables that can be referred in a SELECT Transact-
SQL statement is 256. This limit is documented under the "Maximum
Capacity Specifications" section of the SQL Server Books Online. This
limit includes views and the tables that are referenced in the SELECT
statement. Also, the limit includes the tables and the views that are
referenced by a view that is included in the query. Therefore, if a
table or a view is referenced more than one time in the query, each
reference to the table or the view (or the tables and views that are
referenced in the view) counts against this limit.
With this fix, the maximum limit of the number of tables or views that
are referenced in the query is increased to 260. Therefore, you may
still receive the error message that is mentioned in the "Symptoms"
section of this article."
It seems like I am missing something important; it's a little odd to
make a big fix for a really small improvement.
Thanks.
David WalkerHi David,
You are really a good observer to the KB articles and you are right that
the improvement of the limitation of the numbers of referenced tables and
views from 256 to 260 is not the goal for this hotfix.
In the KB 818406, it is said 'This bug occurs in Microsoft SQL Server 2000
build 613. If you have installed a fix after you installed SQL Server 2000
Service Pack 2 (SP2), you may see this bug. ' However, before install the
fix, you will not encounter the error message described in KB 818406, which
is:
Server: Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The
maximum number of tables in a query (256) was exceeded."
When you have too many views and even when not exceeding the 256 limit.
So, this fix is to resolve this problem. When you installed the fix and you
views and tables are not beyond the 256( this time 260, not to this problem
but it maybe an unintened improvement :-) ), you will not met this problem
any more.
Hope this helps. Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.|||Hi David,
I am reviewing you post and since we have not heard from you for some time
in the newsgroup, I wonder if you still have some question about it besides
my answer. For any more question, please post your message here
and we are glad to help.
Thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.