Limitation fo AND's and OR's

Question

Limitation fo AND's and OR's

karthik M

One Orange Chip

Points: 29139
More actions
June 16, 2008 at 8:05 am

#70323

Hi All,
what is the limitation of AND's and OR's in Sqlserver,sybase,mysql and oracle ?
How to know the maximum level ?
Is there any option to change the level to user defined level ?
Inputs are welcome !
karthik

Viewing 15 posts - 1 through 15 (of 17 total)

You must be logged in to reply to this topic. Login to reply

steveb. SSC-Forever Points: 46733 More actions · Answer 1

I'm not sure if there is a max number of AND OR statements in SQL 2000, Surely the logical and practical limit would always be a lot less than the absolute technical limits.

karthik M One Orange Chip Points: 29139 More actions · Answer 2

anybody want to refer some URL's to know about it, then it would be nice.

karthik

steveb. SSC-Forever Points: 46733 More actions · Answer 3

I am curious why you need this information.

Are you preparing to write the monster SELECT statement and want to see what system can handle it?

Or are you simply doing a comparison between db systems?

Sergiy SSC Guru Points: 110209 More actions · Answer 4

There are limitations:

- if there are more than 2 OR's/AND's in a query the developer must be warned about inappropriate technics he/she is using;

- if there are more than 10 ones developer must be fired.

_____________
Code for TallyGenerator

Carl Federl One Orange Chip Points: 25384 More actions · Answer 5

Ran as test with 19,972 ORs and no problem were encountered. Believe that the only restriction will be the size of a single SQL statement that can be sent to SQL Server, which is the Network Packet Size (defaults to 4Kb) times 65,536 or 256Mb. Of course the network packet size can be increased if that is insufficient.

Alternatively, for "OR"s, the values could be inserted into a table and then joined, so the number of "OR" would be limited by the lesser of disk space or 1,048,516 terabytes.

Reproduction using the AdventerWorks demo database:

1) Run this first and save the output to a text file:

select 'or ContactID = ' + cast(ContactID as varchar(20) )

from Person.Contact

2. Edit the file, remove any headers or trailer information and then prefix with:

select count(*)

from Person.Contact

where 1 = 1

3. Run the SQL in the file.

SQL = Scarcely Qualifies as a Language

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 6

karthikeyan (6/16/2008)
Hi All,
what is the limitation of AND's and OR's in Sqlserver,sybase,mysql and oracle ?
How to know the maximum level ?
Is there any option to change the level to user defined level ?
Inputs are welcome !

Nah... you should know how to do this... If you can't find it in Books Online under "Specifications", then you need to write a test.

But, I agree with Sergiy... if you write 10 in production code, you might need some recalibration during a pork chop feeding time 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 7

p.s. When you do find out, be sure to post the answer... inputs are welcome 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 8

I searched on websites, and found one URL.

http://www.dbforums.com/showthread.php?t=931923

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 9

I am curious why you need this information.

I have executed a query which has more number of ( around 500) string's in the IN clause. (All of us know IN will be treated internally as OR ).

When i execute it in Sql2000, it was executed successfully.

when i execute it in sybase12.0, it throws me the below error

Too many ANDs or ORs in expression (limit 300

per expression level). Try splitting query or limiting ANDs

and ORs.

Then only, i became curious to know the maximum level of AND's and OR's in all the databases.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 10

Reproduction using the AdventerWorks demo database:
1) Run this first and save the output to a text file:
select 'or ContactID = ' + cast(ContactID as varchar(20) )
from Person.Contact
2. Edit the file, remove any headers or trailer information and then prefix with:
select count(*)
from Person.Contact
where 1 = 1
3. Run the SQL in the file.

Carl,

I am not getting this one. Can you explain it ?

karthik

Jeff Moden SSC Guru Points: 1004638 More actions · Answer 11

karthikeyan (6/18/2008)
I have executed a query which has more number of ( around 500) string's in the IN clause. (All of us know IN will be treated internally as OR ).

Ummm... yes... in SQL Server and a couple of other RDBMS's... but no guarantee there for others... how do you know that some RDBMS's won't simply turn the IN list to a working table and join to it?

When you do a test, you have to test what you actually want to test... not some alternative which may not always be true.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

karthik M One Orange Chip Points: 29139 More actions · Answer 12

Just i saw the below URL.

http://answers.google.com/answers/threadview?id=315972

but no guarantee there for others... how do you know that some RDBMS's won't simply turn the IN list to a working table and join to it?

I do agree with you.

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 13

I want to know the MAXIMUM limitations of the following statements.

where could i find the details ?

IF

CASE

AND's

OR's

Number of columns in a SELECT statement

Trigger ( Upto 128 nested level - am i correct ?)

SP calling a SP inside ( upto 32 - am i correct ? )

karthik

karthik M One Orange Chip Points: 29139 More actions · Answer 14

I also studied the below URL. So i am clear with Sybase side.

http://www.mydatabasesupport.com/forums/sybase/239321-sql-error-code-404-too-many-ands-ors-expression.html

Now, I want to know the limitations in Sql2000,sql2005 and sql2008.

can anybody refer some URL's ?

karthik