SQL Server和IN子句中大范围值的效率

Question

我有一个类似于MySQL的关于IN子句的问题，但适用于SQL Server。

具体来说，我正在构建一个可执行的SQL字符串，其中可能包含一个非常长的枚举项列表，用于IN子句（即1000+）。

这是从标准的动态列表构建过滤器的有效方法吗？还是应该将标准数据插入临时表，然后将其JOIN到我的导出表中进行筛选操作？

如果答案不是很直接，每种方法的优点和缺点都会受到赞赏。

如果有人问我这个问题，我深表歉意。链接MySQL的问题相当古老。我想这是SQL Server的副本，但我找不到它。

Answer 1

您忘记告诉我们您使用的SQL Server的版本。当然，每一个新版本都能帮助我们以更有效的方式解决问题。

在SQLServer2005+中，您可以使用这样一个简单的表值函数来实现联接：

CREATE FUNCTION [dbo].[SplitInts]
(
   @List       VARCHAR(MAX),
   @Delimiter  CHAR(1)
)
RETURNS TABLE
AS
   RETURN ( SELECT Item FROM ( SELECT Item = x.i.value('(./text())[1]', 'int') FROM 
            ( SELECT [XML] = CONVERT(XML, '<i>' + REPLACE(@List, @Delimiter, '</i><i>') 
              + '</i>').query('.') ) AS a CROSS APPLY [XML].nodes('i') AS x(i)
          ) AS y WHERE Item IS NOT NULL
   );
GO

现在通过你的大名单并加入：

CREATE PROCEDURE dbo.GetData
    @List VARCHAR(MAX)
AS
BEGIN
    SET NOCOUNT ON;
    SELECT t.col1, t.col2 --, ...
      FROM dbo.DataTable AS t
      INNER JOIN dbo.SplitInts(@List, ',') AS i
      ON t.ColumnID = i.Item;
END
GO

这并不奇怪（我在博客中谈到了各种方法的性能，在5000个值以下几乎没有什么区别），但可能比特别的IN (...huge list...)查询性能更好。

在SQLServer2008+中，可以使用表值参数。与上面类似，您可以将DataTable或C#中的任何结构传递到存储过程中并执行联接。如果你使用的是2008或更高版本，我也可以添加一个例子。

Answer 2

我建议使用分段表方法，并使用where exists(...)子句，例如

select * from Employees e
where exists(select 1 from BigEmplCriteriaTable where criteria=e.EmployeeLoc)