"In" Clause Limitation in SQL Server

IN clause limitation in Sql Server

Yes, there is a limit, but Microsoft only specifies that it lies "in the thousands":

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

Looking at those errors in details, we see that this limit is not specific to IN but applies to query complexity in general:

Error 8623:

The query processor ran out of internal resources and could not produce a query plan. This is a rare event and only expected for extremely complex queries or queries that reference a very large number of tables or partitions. Please simplify the query. If you believe you have received this message in error, contact Customer Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please look for potentially complex expressions in your query, and try to simplify them.

Maximum size for a SQL Server Query? IN clause? Is there a Better Approach

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

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.

Update

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 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;

Limit to number of Items in list for WHERE clause SQL query

Explicitly including an extremely large number of values (many thousands of values separated by commas) within the parentheses, in an IN clause can consume resources and return errors 8623 or 8632. To work around this problem, store the items in the IN list in a table, and use a SELECT subquery within an IN clause.

Error 8623:

The query processor ran out of internal resources and could not
produce a query plan. This is a rare event and only expected for
extremely complex queries or queries that reference a very large
number of tables or partitions. Please simplify the query. If you
believe you have received this message in error, contact Customer
Support Services for more information.

Error 8632:

Internal error: An expression services limit has been reached. Please
look for potentially complex expressions in your query, and try to
simplify them.

microsoft docs

Limit on the WHERE col IN (...) condition

Depending on the database engine you are using, there can be limits on the length of an instruction.

SQL Server has a very large limit:

http://msdn.microsoft.com/en-us/library/ms143432.aspx

ORACLE has a very easy to reach limit on the other side.

So, for large IN clauses, it's better to create a temp table, insert the values and do a JOIN. It works faster also.

SQL IN Clause 1000 item limit

You should transform the IN clauses to INNER JOIN clauses.

You can transform a query like this one

SELECT  foo   
FROM    bar   
WHERE bar.stuff IN  
       (SELECT  stuff FROM asdf)

in a query like this other one.

SELECT  b.foo 
FROM    ( 
        SELECT  DISTINCT stuff 
        FROM    asdf ) a 
JOIN    bar b 
ON      b.stuff = a.stuff

You will also gain a lot of performance

Work around 'IN' clause limitation

Create a table function that will return your string results into a data-set that can be inserted into a CTE, temp table, or used directly in a join. This has been the most effective way for me to get around this limitation. Below is a link to Ole Michelsen's website who provides a simple but flexible solution.

Link: http://ole.michelsen.dk/blog/split-string-to-table-using-transact-sql.html

Sql Query IN clause limit 1 Record

You can add a ROW_NUMBER() function based on the jobHistoryID

SELECT 
    [Job].[JobID],
    [Job].[PriceEstimateNumber],
    [CustomerReadyDate].[JobHistoryDate] As [ConfirmLeadDate1],
    [CustomerReadyDate].[JobColumnID]
FROM
    [tblJob] Job
        ON [Job].[JobID] = [StepJob].[JobID]
        AND [Job].[SubmitTimestamp] > '2019-01-01'
    LEFT JOIN (
            SELECT 
                [JobHistory].[JobID],
                [JobHistory].[JobHistoryDate],
                [JobHistory].[JobColumnID],
                [JobHistory].[JobHistoryID],
                ROW_NUMBER() OVER(ORDER BY [JobHistoryID] DESC) rn
            FROM [dbo].[tblJob_History] AS [JobHistory]
            INNER JOIN [dbo].[tblJob] AS [Job]
                ON [Job].[JobID] = [JobHistory].[JobID] 
            WHERE
                [JobHistory].[jobColumnID] IN (251,252)      
            ) AS [CustomerReadyDate]
            ON [CustomerReadyDate].[JobID] = [Job].[JobID]

WHERE
    [Job].[OrderType] = 'Job'
    AND [Job].LocationTypeID = 1
    AND [CustomerReadyDate].rn = 1

but i think you can optimize you query as the following:

SELECT * FROM (

    SELECT 
        [Job].[JobID],
        [Job].[PriceEstimateNumber],
        [JobHistory].[JobHistoryDate] As [ConfirmLeadDate1],
        [JobHistory].[JobColumnID]
        ROW_NUMBER() OVER(ORDER BY [JobHistoryID] DESC) rn
    FROM [dbo].[tblJob_History] AS [JobHistory]
    INNER JOIN [dbo].[tblJob] AS [Job]
        ON [Job].[JobID] = [JobHistory].[JobID] 
    WHERE [JobHistory].[jobColumnID] IN (251,252)   
    AND [Job].[OrderType] = 'Job'
    AND [Job].LocationTypeID = 1

) AS T1

WHERE T1.rn = 1 

How to overcome resource limitation of IN operator in Microsoft SQL Server

You should transmit data to SQL Server like in related post

Sub UpdateTable()

    Dim cnn As Object
    Dim wbkOpen As Workbook
    Dim objfl As Variant
    Dim rngName As Range
    Workbooks.Open "C:\your_path_here\Excel_to_SQL_Server.xls"
    Set wbkOpen = ActiveWorkbook
    Sheets("Sheet1").Select
    Set rngName = Range(Range("A1"), Range("A1").End(xlToLeft).End(xlDown))
    rngName.Name = "TempRange"
    strFileName = wbkOpen.FullName
    Set cnn = CreateObject("ADODB.Connection")
    cnn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & strFileName & ";Extended Properties=""Excel 12.0 Xml;HDR=Yes"";"
    nSQL = "INSERT INTO [odbc;Driver={SQL Server};Server=Server_Name;Database=[Your_Database].[dbo].[TBL]]"
    nJOIN = " SELECT * from [TempRange]"
    cnn.Execute nSQL & nJOIN
    MsgBox "Uploaded Successfully"
    wbkOpen.Close
    Set wbkOpen = Nothing

End Sub

and then you can do any you want - use operator IN or LEFT JOIN whith check for null on SQL server side.

You can use temporary table if you will not close connection - temp table exists only in session level.

This is way from VBA code.

Second way - you can use SSIS package to work with excel files. In package you will have same steps - data frlow to transmit data from excel to server and then run sql code to join data. Or you can use join block inside SSIS package.

Third way that you can implement if data in SQL server less than excel row count limit. You can import data from SQL into Excel sheet, and than use VLOOKUP() function to join tables and find related data.

---

Related Topics