如何解决 SQL Server 的"The maximum number of tables in a query (260) was exceeded."

Question

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

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

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

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视图分成更小的块:

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各包含大约一半的查询:

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

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

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个表的限制?

参见

• 修复:在SQL Server 2000 SP3中使用视图的Transact-SQL查询可能会意外失败
• MSDN: Maximum Capacity Specifications for SQL Server

Answer 1

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

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

，视图现在调用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是不变的。

Answer 2

您可以将子查询存储到临时表中，例如您提到的USGovCurrencyOnHandBreakdown_Additions和USGovCurrencyOnHandBreakdown_Subtractions，然后从这些临时表中选择而不是视图。

当然，由于脏读，事务可能是一个问题，我不知道在这种情况下这是否值得关注…