SQL Server and Efficiency of Large Range of Values within an IN Clause

Question

I have a case similar to this MySQL question regarding the IN clause, but for SQL Server.

Specifically, I am building an executable SQL string, and within it is the potential for a VERY long list of enumerated items for use within an IN clause (I.E. 1000+).

Is this an efficient way of constructing my filter from a dynamic list of criteria, or should I insert the criteria data into a temp table and then JOIN it to my export table for the filtering operation?

Pros and Cons of each approach would be appreciated if the answer isn't quite straight forward.

I apologize if this question has been asked. The linked MySQL question is rather old. I'd imagine that this is a duplicate for SQL Server, but I can't find it.

Answer 1

You forgot to tell us what version of SQL Server you're using. Of course with each new version we get capabilities that help us solve problems in more efficient ways.

In SQL Server 2005+ you can use a simple table-valued function like this to implement a join:

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

Now pass in your big list and join:

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

This is not fantastic (I blogged about performance of various methods here and under 5000 values there is little difference), but may very well perform better than an ad hoc IN (...huge list...) query.

In SQL Server 2008+ you can use a table-valued parameter. Similarly as above you can pass a DataTable or whatever structure from C# into a stored procedure and perform a join. If you're using 2008 or above I can add an example for that too.

Answer 2

I suggest the staging table approach, and use a where exists(...) clause, e.g.

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