Sql Server 中的“IN”子句限制
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21178390/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
"IN" clause limitation in Sql Server
提问by MSanika
Does anybody know what is the limit for the number of values one can have in a list of expressions (to test for a match) for the IN clause?
有谁知道在 IN 子句的表达式列表（以测试匹配）中可以拥有的值数量的限制是多少？
采纳答案by Heinzi
Yes, there is a limit, but MSDN only specifies that it lies "in the thousands":
是的，有一个限制，但MSDN 只指定它位于“成千上万”：
Including an extremely large number of values (many thousands) in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table.
在 IN 子句中包含非常多的值（数千个）会消耗资源并返回错误 8623 或 8632。要解决此问题，请将 IN 列表中的项目存储在表中。
Looking at those errors in details, we see that this limit is not specific to INbut applies to query complexity in general:
详细查看这些错误，我们看到此限制并非特定于IN但适用于一般查询复杂性：
Error 8623:
The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.
Error 8632:
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
错误 8623：
查询处理器耗尽了内部资源，无法生成查询计划。这是一种罕见的事件，仅适用于极其复杂的查询或引用大量表或分区的查询。请简化查询。如果您认为自己错误地收到了此消息，请联系客户支持服务以获取更多信息。
错误 8632：
内部错误：已达到表达式服务限制。请在您的查询中寻找潜在的复杂表达式，并尝试简化它们。
回答by Nick van Esch
It is not specific but is related to the query plan generator exceeding memory limits. I can confirm that with several thousand it often errors but can be resolved by inserting the values into a table first and rephrase the query as
它不是特定的，但与超出内存限制的查询计划生成器有关。我可以确认有几千个它经常出错，但可以通过首先将值插入表中并将查询重新表述为
select * from b where z in (select z from c)
where the values you want in the in clause are in table c. We used this successfully with an in clause of 1-million values.
in 子句中所需的值在表 c 中。我们成功地使用了一个包含 100 万个值的 in 子句。
回答by Amarnath Balasubramanian
Depending on the database engine you are using, there can be limits on the length of an instruction.
根据您使用的数据库引擎，指令的长度可能会有限制。
SQL Server has a very large limit:
SQL Server 有一个非常大的限制：
Maximum Capacity Specifications for SQL Server
SQL Server 的最大容量规范
So, for large IN clauses, it's better to create a temp table, insert the values and do a JOIN. It works faster also.
因此，对于大 IN 子句，最好创建一个临时表，插入值并执行 JOIN。它的工作速度也更快。
There is a limit, but you can split your values into separate blocks of in()
有一个限制，但您可以将您的值拆分为单独的 in() 块
Select *
From table
Where Col IN (123,123,222,....)
or Col IN (456,878,888,....)
use a table valued parameter in 2008, or some approach described here
使用 2008 年的表值参数，或此处描述的某些方法
回答by icyerasor
Depending on how you execute the query (JDBC, Hiberante, some kind of SQL-GUI) and when using literal values instead of a sub-query where X in (1, 2, 3...)- you may also encounter the following error:
根据您执行查询的方式（JDBC、Hiberante、某种 SQL-GUI）以及使用文字值而不是子查询时where X in (1, 2, 3...)- 您可能还会遇到以下错误：
Too many parameters were provided in this RPC request. The maximum is 2100.
此 RPC 请求中提供的参数过多。最大值为 2100。
So the limit would be 2100 (or even less when other parameters are present, too) in those cases.
因此，在这些情况下，限制为 2100（或者在存在其他参数时甚至更少）。
