maximum number of subqueries possible

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

maximum number of subqueries possible

Author

Topic

krishnan_sn
Starting Member

14 Posts

Posted - 2003-12-19 : 01:52:53

hi,

how many number of subqueries i can have in a single statement...is there any maximum for it...

like...32 for stored procedures...what i mean is a stored proc calling another stor proc and that proc calling another...

like this how much is possible in the subqueries

sample like ...
use pubs
go
select max(job_id) as jobId from employee where (job_id<(select max(job_id) From employee where (job_id<(select max(job_id) From employee))))

thanks

S.Navaneetha Krishnan

robvolk
Most Valuable Yak

15732 Posts

Posted - 2003-12-19 : 08:21:33

I vaguely remember the number 16 for some reason, but it's probably from v6.5 days. In any case, anything more than 16 subqueries is not going to be very efficient.

What exactly are you trying to do with the query?

Arnold Fribble
Yak-finder General

1961 Posts

Posted - 2003-12-19 : 08:58:03

Appears to be getting the n-th largest (distinct) value by physically nesting n queries.
In an attempt to answer the question, I tried about 30 and my (development) server wouldn't quit attempting to generate an execution plan: wouldn't even restart SQL Server. In the end I rebooted the machine!
Anyway, with a large enough table, this was getting very inefficient: it was doing scans (in the wrong direction) on everything but the innermost SELECT.
As an alternative, moving the subqueries seems to have a good effect. With a 100000 row tally table Numbers, these two queries give relative cost estimates of 98.81% to 1.19%:


SELECT MAX(n) FROM Numbers WHERE n<(
SELECT MAX(n) FROM Numbers WHERE n<(
SELECT MAX(n) FROM Numbers WHERE n<(
SELECT MAX(n) FROM Numbers WHERE n<(
SELECT MAX(n) FROM Numbers
)
)
)
)

SELECT (SELECT MAX(n) FROM Numbers WHERE n < m) AS m FROM (
SELECT (SELECT MAX(n) FROM Numbers WHERE n < m) AS m FROM (
SELECT (SELECT MAX(n) FROM Numbers WHERE n < m) AS m FROM (
SELECT (SELECT MAX(n) FROM Numbers WHERE n < m) AS m FROM (
SELECT MAX(n) AS m FROM Numbers
) AS A
) AS A
) AS A
) AS A

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources