Have you ever encountered a query that SQL Server could not execute because it referenced too many tables?

Question

Have you ever seen any of there error messages?

-- SQL Server 2000

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

-- SQL Server 2005

Too many table names in the query. The maximum allowable is 256.

If yes, what have you done?

Given up? Convinced the customer to simplify their demands? Denormalized the database?

---

@(everyone wanting me to post the query):

1. I'm not sure if I can paste 70 kilobytes of code in the answer editing window.
2. Even if I can this this won't help since this 70 kilobytes of code will reference 20 or 30 views that I would also have to post since otherwise the code will be meaningless.

I don't want to sound like I am boasting here but the problem is not in the queries. The queries are optimal (or at least almost optimal). I have spent countless hours optimizing them, looking for every single column and every single table that can be removed. Imagine a report that has 200 or 300 columns that has to be filled with a single SELECT statement (because that's how it was designed a few years ago when it was still a small report).

Answer 1

For SQL Server 2005, I'd recommend using table variables and partially building the data as you go.

To do this, create a table variable that represents your final result set you want to send to the user.

Then find your primary table (say the orders table in your example above) and pull that data, plus a bit of supplementary data that is only say one join away (customer name, product name). You can do a SELECT INTO to put this straight into your table variable.

From there, iterate through the table and for each row, do a bunch of small SELECT queries that retrieves all the supplemental data you need for your result set. Insert these into each column as you go.

Once complete, you can then do a simple SELECT * from your table variable and return this result set to the user.

I don't have any hard numbers for this, but there have been three distinct instances that I have worked on to date where doing these smaller queries has actually worked faster than doing one massive select query with a bunch of joins.