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

2021-12-01 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 个项目限制的东西，但你可以通过将 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>

然后对文档和表格执行某种 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 将达到 SO 在 IN 中大约 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;

相关文章