SQL Server查询的最大大小? IN子句?有没有更好的方法 [英] Maximum size for a SQL Server Query? IN clause? Is there a Better Approach

问题描述

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

Possible Duplicate:
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进行代码中的JOIN"操作，那是最好的方法吗?是否要将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批处理都必须符合批量大小限制:65536 *网络数据包大小.

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中以大约1万个值达到因为是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;

这篇关于SQL Server查询的最大大小? IN子句?有没有更好的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！