<p>Have you ever seen any of there error messages?</p>
<blockquote>
<p>-- SQL Server 2000</p>
<p>Could not allocate ancillary table for view or function resolution.<br />
The maximum number of tables in a query (256) was exceeded.</p>
<p>-- SQL Server 2005</p>
<p>Too many table names in the query. The maximum allowable is 256.</p>
</blockquote>
<p>If yes, what have you done?</p>
<p>Given up? Convinced the customer to simplify their demands? Denormalized the database?</p>
<hr />
<p>@(everyone wanting me to post the query):</p>
<ol>
<li>I'm not sure if I can paste 70 kilobytes of code in the answer editing window.</li>
<li>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.</li>
</ol>
<p>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).</p>

[{'answer_id': 2440, 'author': 'ZombieSheep', 'author_id': 377, 'author_profile': 'https://Stackoverflow.com/users/377', 'pm_score': 1, 'selected': False, 'text': "<p>I have never come across this kind of situation, and to be honest the idea of referencing &gt; 256 tables in a query fills me with a mortal dread.</p>\n<p>Your first question should probably by &quot;Why so many?&quot;, closely followed by &quot;what bits of information do I <em>NOT</em> need?&quot;  I'd be worried that the amount of data being returned from such a query would begin to impact performance of the application quite severely, too.</p>\n"}, {'answer_id': 2442, 'author': 'Shawn', 'author_id': 26, 'author_profile': 'https://Stackoverflow.com/users/26', 'pm_score': 0, 'selected': False, 'text': "<p>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.</p>\n"}, {'answer_id': 2460, 'author': 'pix0r', 'author_id': 72, 'author_profile': 'https://Stackoverflow.com/users/72', 'pm_score': 1, 'selected': False, 'text': "<p>@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.</p>\n"}, {'answer_id': 2462, 'author': 'Dillie-O', 'author_id': 71, 'author_profile': 'https://Stackoverflow.com/users/71', 'pm_score': 4, 'selected': True, 'text': "<p>For SQL Server 2005, I'd recommend using table variables and partially building the data as you go. </p>\n\n<p>To do this, create a table variable that represents your final result set you want to send to the user.</p>\n\n<p>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.</p>\n\n<p>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.</p>\n\n<p>Once complete, you can then do a simple SELECT * from your table variable and return this result set to the user.</p>\n\n<p>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.</p>\n"}, {'answer_id': 2468, 'author': 'Shawn', 'author_id': 26, 'author_profile': 'https://Stackoverflow.com/users/26', 'pm_score': 0, 'selected': False, 'text': '<p>Post the query :D</p>\n\n<p>Also I feel like one of the possible problems could be having a ton (read 200+) of name/value tables which could condensed into a single lookup table.</p>\n'}, {'answer_id': 256991, 'author': 'friism', 'author_id': 2942, 'author_profile': 'https://Stackoverflow.com/users/2942', 'pm_score': 1, 'selected': False, 'text': '<p>This would happen all the time when writing Reporting Services Reports for Dynamics CRM installations running on SQL Server 2000. CRM has a nicely normalised data schema which results in a lot of joins. There\'s actually a hotfix around that will up the limit from 256 to a whopping 260: <a href="http://support.microsoft.com/kb/818406" rel="nofollow noreferrer">http://support.microsoft.com/kb/818406</a> (we always thought this a great joke on the part of the SQL Server team).</p>\n\n<p>The solution, as Dillie-O aludes to, is to identify appropriate "sub-joins" (preferably ones that are used multiple times) and factor them out into temp-table variables that you then use in your main joins. It\'s a major PIA and often kills performance. I\'m sorry for you.</p>\n\n<p>@Kevin, love that tee -- says it all :-).</p>\n'}, {'answer_id': 3524470, 'author': 'LeRoy DeNooyer', 'author_id': 425546, 'author_profile': 'https://Stackoverflow.com/users/425546', 'pm_score': 0, 'selected': False, 'text': "<p>I had this same problem... my development box runs SQL Server 2008 (the view worked fine) but on production (with SQL Server 2005) the view didn't.  I ended up creating views to avoid this limitation, using the new views as part of the query in the view that threw the error.</p>\n\n<p>Kind of silly considering the logical execution is the same...</p>\n"}, {'answer_id': 9604856, 'author': 'Tamas Pataky', 'author_id': 967497, 'author_profile': 'https://Stackoverflow.com/users/967497', 'pm_score': 0, 'selected': False, 'text': '<p>Had the same issue in SQL Server 2005 (worked in 2008) when I wanted to create a view. I resolved the issue by creating a stored procedure instead of a view.</p>\n'}]
['https://Stackoverflow.com/questions/2432', 'https://Stackoverflow.com', 'https://Stackoverflow.com/users/95/']