如何解决 SQL Server 的“超出查询中的最大表数 (260)". [英] How to work around SQL Server's "The maximum number of tables in a query (260) was exceeded."

问题描述

我有一个包含一系列 21 个 UNION 的查询，例如:

i have a query that contains a series of 21 UNIONs, e.g.:

CREATE VIEW dbo.USGovCurrencyOnHandBreakdown AS

   SELECT ... FROM a
   UNION ALL
   SELECT ... FROM b
   UNION ALL
   SELECT ... FROM c
   UNION ALL
   SELECT ... FROM d
   ...
   UNION ALL
   SELECT ... FROM u

查询在单独运行时运行良好.但是当查询通过包含视图运行时:

The query runs fine when run alone. But when the query is run through the containing view:

SELECT * FROM USGovCurrencyOnHandBreakdown 

Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.

我尝试将我的 USGovFedExpentiures 视图拆分成更小的块:

i've tried splitting up my USGovFedExpentiures view into smaller chunks:

CREATE VIEW dbo.USGovCurrencyOnHandBreakdown AS

   SELECT x FROM TreasuryAuditResults
   UNION ALL
   SELECT x FROM USGovCurrencyOnHandBreakdown_Additions
   UNION ALL
   SELECT x FROM USGovCurrencyOnHandBreakdown_Subtractions

使用 USGovCurrencyOnHandBreakdown_Additions 和 USGovCurrencyOnHandBreakdown_Subtractions 分别包含大约一半的查询:

With USGovCurrencyOnHandBreakdown_Additions and USGovCurrencyOnHandBreakdown_Subtractions each containing roughly half the queries:

CREATE VIEW USGovCurrencyOnHandBreakdown_Additions AS
   SELECT ... FROM b
   UNION ALL
   SELECT ... FROM c
   ...
   SELECT ... FROM k

CREATE VIEW USGovCurrencyOnHandBreakdown_Subtractions AS
   SELECT ... FROM l
   UNION ALL
   SELECT ... FROM m
   ...
   SELECT ... FROM u

但是从父"视图中选择仍然失败:

But selecting from the "parent" view still fails:

SELECT * FROM USGovCurrencyOnHandBreakdown

Msg 4414, Level 16, State 1, Line 1
Could not allocate ancillary table for view or function resolution. The maximum number of tables in a query (260) was exceeded.

如何解决 256 个表的限制?

How can i work around the 256 table limit?

• 修复:在 SQL Server 2000 SP3 中使用视图的 Transact-SQL 查询可能会意外失败
• MSDN:SQL Server 的最大容量规范

推荐答案

一位同事想出了一个很好的答案.使用函数返回表变量；将结果一点一点地插入到表变量中:

A colleague came up with a great answer. Use a function to return a table variable; insert the results into the table variable bit by bit:

CREATE VIEW dbo.USGovCurrencyOnHandBreakdown AS
    SELECT * FROM fn_USGovCurrencyOnHandBreakdown()

视图现在调用 UDF:

with the view now calling the UDF:

CREATE FUNCTION dbo.fn_USGovCurrencyOnHandBreakdown()
    RETURNS @Results TABLE
    (
        Total money, 
        ...
    )

    INSERT INTO @Results SELECT ... FROM a
    INSERT INTO @Results SELECT ... FROM b
    INSERT INTO @Results SELECT ... FROM c
    INSERT INTO @Results SELECT ... FROM d
      ...
    INSERT INTO @Results SELECT ... FROM u

    RETURN 
END

据任何客户所知，view 没有改变.(除了现在它有效！)

As far as any clients know the view is unchanged. (Except now it works!)

这篇关于如何解决 SQL Server 的“超出查询中的最大表数 (260)".的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！