您是否遇到过 SQL Server 由于引用了太多表而无法执行的查询？
您见过任何错误消息吗？
--SQL Server 2000
无法为视图或函数解析分配辅助表。
超出了查询中的最大表数 (256)。--SQL Server 2005
查询中的表名太多。 允许的最大值为 256。
如果是，您做了什么？
放弃了？ 说服客户简化他们的需求？ 数据库非规范化？
@（每个人都希望我发布查询）：
- 我不确定是否可以在答案编辑窗口中粘贴 70 KB 的代码。
- 即使我能做到这一点，这也无济于事，因为这 70 KB 的代码将引用 20 或 30 个视图，我也必须发布这些视图，否则代码将毫无意义。
我不想听起来像是我在吹嘘，但问题不在于查询。 查询是最优的（或至少几乎是最优的）。 我花了无数的时间来优化它们，寻找可以删除的每一列和每一个表。 想象一个有 200 或 300 列的报表，必须用单个 SELECT 语句填充（因为几年前它还是一个小报表时就是这样设计的）。
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):
- I'm not sure if I can paste 70 kilobytes of code in the answer editing window.
- 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).
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（8）
对于 SQL Server 2005，我建议使用表变量并部分构建数据。
为此，请创建一个表变量来表示要发送给用户的最终结果集。
然后找到您的主表（例如上面示例中的订单表）并提取该数据，再加上一些仅表示一次连接的补充数据（客户名称、产品名称）。 您可以执行 SELECT INTO 将其直接放入表变量中。
从那里，迭代表并为每一行执行一系列小型 SELECT 查询，以检索结果集所需的所有补充数据。 随时将它们插入到每列中。
完成后，您可以从表变量中执行简单的 SELECT * 操作，并将结果集返回给用户。
我对此没有任何硬性数字，但迄今为止，我已经研究过三个不同的实例，在这些实例中，执行这些较小的查询实际上比使用一堆连接执行一个大规模选择查询要快。
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.
@chopeen您可以更改计算这些统计信息的方式，而是保留所有每个产品统计信息的单独表格。下订单时，循环遍历产品并更新统计信息表中的相应记录。 这会将大量计算负载转移到结帐页面，而不是在运行报告时在一个巨大的查询中运行所有内容。 当然，有些统计数据无法以这种方式发挥作用，例如在购买特定产品后跟踪客户的下一次购买。
@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.
我从来没有遇到过这种情况，说实话参考>的想法 查询中的 256 个表让我感到非常恐惧。
您的第一个问题可能应该是“为什么这么多？”，紧随其后的是“我不需要哪些信息？” 我担心从此类查询返回的数据量也会开始严重影响应用程序的性能。
I have never come across this kind of situation, and to be honest the idea of referencing > 256 tables in a query fills me with a mortal dread.
Your first question should probably by "Why so many?", closely followed by "what bits of information do I NOT need?" 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.
当为在 SQL Server 2000 上运行的 Dynamics CRM 安装编写 Reporting Services 报表时，这种情况经常会发生。CRM 具有良好规范化的数据架构，这会导致大量联接。 实际上有一个修补程序可以将限制从 256 提高到高达 260：http://support.microsoft .com/kb/818406（我们一直认为这是 SQL Server 团队的一个天大的笑话）。
正如 Dillie-O 所提到的，解决方案是识别适当的“子连接”（最好是多次使用的子连接）并将它们分解为临时表变量，然后在主连接中使用。 这是一个主要的 PIA，并且常常会影响性能。 我为你感到难过。
@Kevin，喜欢那件 T 恤——说明了一切:-)。
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: http://support.microsoft.com/kb/818406 (we always thought this a great joke on the part of the SQL Server team).
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.
@Kevin, love that tee -- says it all :-).
我也遇到了同样的问题...我的开发盒运行 SQL Server 2008（视图工作正常），但在生产环境（使用 SQL Server 2005）视图却没有。 我最终创建了视图来避免此限制，使用新视图作为引发错误的视图中查询的一部分。
考虑到逻辑执行是相同的，有点愚蠢......
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.
Kind of silly considering the logical execution is the same...
我想看到这个查询，但我想这是某种迭代器的问题，虽然我想不出任何可能的情况，但我敢打赌它来自一个糟糕的 while/case/cursor 或大量意见落实不力。
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.
发布查询：D
另外，我觉得可能的问题之一可能是拥有大量（读取 200+）名称/值表，这些表可以压缩为单个查找表。
Post the query :D
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.
当我想创建视图时，在 SQL Server 2005（2008 年工作）中遇到了同样的问题。 我通过创建存储过程而不是视图解决了该问题。
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.