Number of characters in a "IN" clause

Hi
Is there any limits on how many characters I can have in an "IN" clause
(e.g. select ID from table where Name IN (test, test1....etc..)?
Reason for asking is that I have a statement that fails when I have more
than 500 characters in the "IN" clause, so I was wondering if it's due to
limitations in the IN clause or if it's something else that restrict the
number of characters.
Regards
SteenJust that the total batch can not be > 64K
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
news:OUAPZ2gLFHA.1948@.TK2MSFTNGP14.phx.gbl...
> Hi
> Is there any limits on how many characters I can have in an "IN" clause
> (e.g. select ID from table where Name IN (test, test1....etc..)?
> Reason for asking is that I have a statement that fails when I have more
> than 500 characters in the "IN" clause, so I was wondering if it's due to
> limitations in the IN clause or if it's something else that restrict the
> number of characters.
> Regards
> Steen
>|||Thanks Wayne...
In the meantime I actually found out that it isn't SQL as such that holds
the limitation. I tried to add more parameters to the query in QA and here
it works fine. It seems like it's the application that cuts the IN clause to
max. 500 (or 499) characters, so they must have set a limit somewhere. If
there's a reason for that or not is then the question, that I'll have to
discuss with the vendor.
Regards
Steen
Wayne Snyder wrote:[vbcol=seagreen]
> Just that the total batch can not be > 64K
>
> "Steen Persson" <SPE@.REMOVEdatea.dk> wrote in message
> news:OUAPZ2gLFHA.1948@.TK2MSFTNGP14.phx.gbl...|||If you mean number of items in the IN clause, then I would not recommend
hundreds of items. After some 500 items, the compilation time grows very
fast, making the query very slow. In those cases, it is better to store
all these items in a table, and use EXISTS or join with the table.
HTH,
Gert-Jan
Steen Persson wrote:
> Hi
> Is there any limits on how many characters I can have in an "IN" clause
> (e.g. select ID from table where Name IN (test, test1....etc..)?
> Reason for asking is that I have a statement that fails when I have more
> than 500 characters in the "IN" clause, so I was wondering if it's due to
> limitations in the IN clause or if it's something else that restrict the
> number of characters.
> Regards
> Steen|||Hi Gert-Jan
I agree with you, but unfortunately that's the way the application has been
designed by the vendor. It seems like the developers have never thought that
you'd add so many options in the application - and this is not the only
"poor" solution they have programmed...:-(
Regards
Steen
Gert-Jan Strik wrote:[vbcol=seagreen]
> If you mean number of items in the IN clause, then I would not
> recommend hundreds of items. After some 500 items, the compilation
> time grows very fast, making the query very slow. In those cases, it
> is better to store all these items in a table, and use EXISTS or join
> with the table.
> HTH,
> Gert-Jan
>
> Steen Persson wrote: