In this blog post, let’s learn about the error message “146 – Could not allocate ancillary table for a subquery. Maximum number of tables in a query (%d) exceeded.” in Microsoft SQL Server, the reason why it appears and the solution to fix it.
SQL Server Error Message
146 – Could not allocate ancillary table for a subquery. Maximum number of tables in a query (%d) exceeded.
Reason for the Error
Suppose we have the following query:
SELECT *
FROM Sales.SalesOrderHeader
WHERE SalesOrderID IN (
SELECT SalesOrderID
FROM Sales.SalesOrderDetail
WHERE OrderQty > 50
);
The SalesOrderHeader table is filtered using a subquery based on a condition in the SalesOrderDetail table. However, if the SalesOrderDetail table is sufficiently large and the subquery is too sophisticated, the query optimizer may allocate an auxiliary table to execute the query. SQL Server will return Error Msg 146 if the maximum number of tables in a query is exceeded during the allocation procedure.
Solution
To fix this problem, we can reduce the subquery or divide it into smaller parts. Depending on the situation, we can also consider indexing the query or rewriting it using a join rather than a subquery.
