2006/02/07

Maximum number of tables per SELECT statement

Have you ever wondered whether the number of tables that you can use in a single SELECT statement is limited or not?

Well, I haven't. But there is a limit. And, contrary to many maximum capacities of SQL Server 2000 specified here (like Databases per instance of SQL Server - 32,767, for example), it is possible to reach it.

When you do, you will see the following error:

Could not allocate ancillary table for view or function resolution.
The maximum number of tables in a query (256) was exceeded.

UPDATE:

I started a discussion about this problem at StackOverflow.