SQL Server 查询的最大大小?IN 子句?有没有更好的方法

2022-01-30 00:00:00 sql tsql sql-server limits

可能重复:
T-SQL WHERE col IN (…)

SQL Server 查询的最大大小是多少?(字符数)

What is the maximum size for a SQL Server query? (# of characters)

IN 子句的最大大小?我想我看到 Oracle 有 1000 个项目的限制，但你可以通过 ANDing 2 个 IN 一起解决这个问题.SQL Server 中的类似问题?

Max size for an IN clause? I think I saw something about Oracle having a 1000 item limit but you could get around this with ANDing 2 INs together. Similar issue in SQL Server?

更新那么，如果我需要从另一个系统(非关系数据库)获取 1000 个 GUID 并对 SQL Server 执行代码中的加入"，那么最好的方法是什么?是将 1000 个 GUID 的列表提交给 IN 子句吗?还是有其他更有效的技术?

UPDATE So what would be the best approach if I need to take say 1000 GUIDs from another system (Non Relational Database) and do a "JOIN in code' against the SQL Server? Is it to submit the list of 1000 GUIDs to an IN clause? Or is there another technique that works more efficiently?

我尚未对此进行测试，但我想知道是否可以将 GUID 作为 XML 文档提交.例如

I haven't tested this but I wonder if I could submit the GUIDs as an XML doc. For example 

<guids>
    <guid>809674df-1c22-46eb-bf9a-33dc78beb44a</guid>
    <guid>257f537f-9c6b-4f14-a90c-ee613b4287f3</guid>
</guids>

然后针对 Doc 和 Table 执行某种 XQuery JOIN.效率低于 1000 项 IN 子句?

and then do some kind of XQuery JOIN against the Doc and the Table. Less efficient than 1000 item IN clause?

推荐答案

每个 SQL 批处理都必须适合 批量大小限制:65,536 * 网络数据包大小.

Every SQL batch has to fit in the Batch Size Limit: 65,536 * Network Packet Size.

除此之外，您的查询还受到运行时条件的限制.它通常会用完堆栈大小，因为 x IN (a,b,c) 只不过是 x=a OR x=b OR x=c ，它创建类似于 x=a OR (x=b OR (x=c))，所以它在大量 OR 的情况下变得非常深.SQL 7 会在 IN 中达到 SO 大约 10k 值，但现在堆栈要深得多(因为 x64)，所以它可以走得很深.

Other than that, your query is limited by runtime conditions. It will usually run out of stack size because x IN (a,b,c) is nothing but x=a OR x=b OR x=c which creates an expression tree similar to x=a OR (x=b OR (x=c)), so it gets very deep with a large number of OR. SQL 7 would hit a SO at about 10k values in the IN, but nowdays stacks are much deeper (because of x64), so it can go pretty deep.

更新

您已经找到了 Erland 关于将列表/数组传递给 SQL Server 的文章.在 SQL 2008 中，您还可以使用 表值参数 来传递整个 DataTable 作为单个表类型参数并加入它.

You already found Erland's article on the topic of passing lists/arrays to SQL Server. With SQL 2008 you also have Table Valued Parameters which allow you to pass an entire DataTable as a single table type parameter and join on it.

XML 和 XPath 是另一个可行的解决方案:

XML and XPath is another viable solution:

SELECT ...
FROM Table
JOIN (
   SELECT x.value(N'.',N'uniqueidentifier') as guid
   FROM @values.nodes(N'/guids/guid') t(x)) as guids
 ON Table.guid = guids.guid;

相关文章