SQL Server：UNION 数量的限制是多少？
我想知道我可以发送到 SQL Server 的 UNION ed SELECT 语句的数量是否有限制？我可能在一条 SQL 语句中完成了多达 36 个操作，因此从字符角度看，它会变得相当长。 （此外，每个 SELECT 语句本身都有些复杂，其中包含 CASE WHEN 语句。）
I was wondering if there's a limit on a number of UNION'ed SELECT statements I can send to an SQL Server? I may have up to 36 done in one SQL statement, so it becomes quite long, character wise. (Also each of those SELECT statements is a somewhat complex by itself with CASE WHEN statements in it.)
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
来自SQL 2008 BOL
“任意数量的UNION运算符可以出现在Transact-SQL语句中”
也来自 MSDN“SQL Server 的最大容量规范” (2008):
批量大小 (1) 65,536 * 网络数据包大小
包含 SQL 语句的字符串长度（批量大小）(1) < strong>65,536 * 网络数据包大小
(1) 网络数据包大小是用于在应用程序和关系数据库引擎之间进行通信的表格数据流 (TDS) 数据包的大小。默认数据包大小为 4 KB，由网络数据包大小配置选项控制。
在我看来，使用默认值时这意味着 268,435,456 字节。请说您的查询比这短。
From SQL 2008 BOL
"Any number of UNION operators can appear in a Transact-SQL statement"
Also from MSDN "Maximum Capacity Specifications for SQL Server" (2008):
Batch size (1) 65,536 * Network Packet Size
Length of a string containing SQL statements (batch size) (1) 65,536 * Network packet size
(1) Network Packet Size is the size of the tabular data stream (TDS) packets used to communicate between applications and the relational Database Engine. The default packet size is 4 KB, and is controlled by the network packet size configuration option.
To my mind, that means 268,435,456 bytes when using defaults. Please say your query is shorter than that.
死灵术，因为我现在知道现实生活中的答案。
从技术上讲，就像尼尔·莫斯所说，> 2.68 亿字节（ASCII 字符），或 > 1.34 亿个 UTF-16 字符。
但是，存在“每个 SELECT 语句 256 个表”的限制。
虽然您可以在查询中使用超过 256 个表WITH UNION STATEMENTS，但此类查询不能用作视图、表值函数或用作某个查询的子查询选择语句。
此外，如果直接使用此类查询，则可能会在 1'300-1'500 个 SELECT 左右达到查询优化器的堆栈空间限制。
我知道，因为感谢一个伟大的（警告 - 讽刺）OCP（原始代码“程序员”），我们有这样的 af*ed up 模式，而我实际上遇到了 256 和 1'300-1'500 限制。
（每个对象类型一个表，以防有人想知道；每个表具有完全相同的列模式（但不是名称） - 而不是具有对象类型的引用表...）
Necromancing, because I now know the real-life answer.
Technically, like Neil Moss said, > 268 million bytes (ASCII characters), or > 134 million UTF-16 characters.
However, there is a limit of "256 tables per SELECT statement".
And while you can use more than 256 tables in a query WITH UNION STATEMENTS, such a query cannot be used as a view, table-valued function or as a subquery for a SELECT statement.
Also, if you use such a query directly, you may hit the stack space limit of the query optimizer at around 1'300-1'500 SELECTs.
I know, because thanks to a great (warning - sarcasm) OCP (original code "programmer"), we have such a f*ed up schema, and I actually ran into both, the 256 and the 1'300-1'500 limit.
(one table per object type, in case anybody wonders; with exactly the same column-schema (but not names) per table - instead of a reference table with object-type...)
根据此 针对他的特定查询，请在 8000 到 16000 之间的某个位置发帖。可能主要由 SQL Server 可用的资源控制。
对于不需要这么多联合的替代方法，可能值得就您所拥有的特定情况提出另一个问题。
According to this post somwhere between 8000 and 16000, for his particular query. Is probably mostly goverened by the resources available to your SQL Server.
It might be worth asking another question about the particular case you have for an alternative method that doesn't need so many unions.