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子句的表达式列表（测试匹配）中可以有多少值的限制吗？
4 个解决方案
#1
14
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 IN but applies to query complexity in general:
详细查看这些错误，我们发现此限制并非特定于IN，但通常适用于查询复杂性：
Error 8623:
错误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:
错误8632：
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
内部错误：已达到表达式服务限制。请在查询中查找可能复杂的表达式，并尝试简化它们。
#2
6
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子句。
#3
3
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
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年使用表值参数，或此处描述的一些方法
Arrays and Lists in SQL Server 2005
SQL Server 2005中的数组和列表
#4
0
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）以及使用文字值而不是其中X在（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（或者当存在其他参数时甚至更少）。
#1
14
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 IN but applies to query complexity in general:
详细查看这些错误，我们发现此限制并非特定于IN，但通常适用于查询复杂性：
Error 8623:
错误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:
错误8632：
Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.
内部错误：已达到表达式服务限制。请在查询中查找可能复杂的表达式，并尝试简化它们。
#2
6
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子句。
#3
3
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
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年使用表值参数，或此处描述的一些方法
Arrays and Lists in SQL Server 2005
SQL Server 2005中的数组和列表
#4
0
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）以及使用文字值而不是其中X在（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（或者当存在其他参数时甚至更少）。