Have you ever encountered a query that SQL Server

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

@chopeen You could change the way you're calculating these statistics, and instead keep a separate table of all per-product stats.. when an order is placed, loop through the products and update the appropriate records in the stats table. This would shift a lot of the calculation load to the checkout page rather than running everything in one huge query when running a report. Of course there are some stats that aren't going to work as well this way, e.g. tracking customers' next purchases after purchasing a particular product.

Answer 2

I'd like to see that query, but I imagine it's some problem with some sort of iterator, and while I can't think of any situations where its possible, I bet it's from a bad while/case/cursor or a ton of poorly implemented views.