Is there any limitation in SQL server IN clause?
Question
Tuesday, October 28, 2008 5:14 AM
I know there is limitation in ORACLE database IN clause, only 1000 expression supported.
Select * from TABLE where ID in (1,2,...1000)
Is there any limitation in SQL server IN clause?
All replies (5)
Tuesday, October 28, 2008 12:44 PM âś…Answered
There is no limitation in SQL Server w.r.t in caluse. you can give n number of values
Wednesday, April 6, 2011 7:20 AM
I dont know limitation number but i had problem yesterday with in clause.Same query work with little then subquery data. But not return resultset with big subquery data. In same query i changed IN CLAUSE to EXISTS and my problem solved.I'll never use IN clause with big subquery data.
Tuesday, September 17, 2013 6:16 PM
well, actually there is. you cannot put infinitely many items in IN statement. There is a limit to that: batch size. http://msdn.microsoft.com/en-us/library/ms143432.aspx
Tuesday, September 17, 2013 6:29 PM | 1 vote
That is not true.
Yes, there is an internal buffer used for the IN list. However there can be many buffers. This is what causes the huge difference in performance when you use huge IN lists. You are much better off creating temp tables and inner joining.
Tuesday, September 17, 2013 10:43 PM | 1 vote
Hello ,
In http://msdn.microsoft.com/en-us/library/ms177682.aspx , I found in the Remarks part :
"Including an extremely large number of values (many thousands) 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.
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."
I noticed the idea " store the items in the IN list in a table". I don't know the consequences of this idea but I am not sure that this forum is for this kind of question ( rather Transact-SQL or Database Engine should be the best forums especially the 1st one ). And I am not sure that a move should be a good idea for this old thread ( October 2008 ).
Have a nice day
Mark Post as helpful if it provides any help.Otherwise,leave it as it is.