Limits On Union (Theoretical Or Otherwise)

Erin Peterson

unread,

Jul 19, 2002, 12:52:18 PM7/19/02

to

Hi all.

Just a quick question about Union.

Is there a limit (theoretical or otherwise) on how many select statements
whose result sets are joined together by UNION?

TIA,
Erin

PS: The answer will apply to MS SQL Server 7.

Anith Sen

unread,

Jul 19, 2002, 1:41:14 PM7/19/02

to

No, there is no limit to the number of SELECT statements
that can be combined using UNION.

As a side note, the combined length of the SQL Query string
should be Network Packet size * 64 MB. Generally Network
Packet size is defaulted to 4 and hence it is 256 MB.

--
- Anith

Erin Peterson

unread,

Jul 19, 2002, 1:49:03 PM7/19/02

to

Thanks for the info Anith.

Anith Sen wrote in message ...

Kresimir Radosevic

unread,

Jul 19, 2002, 2:03:43 PM7/19/02

to

Anith, post an example :)

Kresimir Radosevic

"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:uyr7Dt0LCHA.2088@tkmsftngp11...

Steve Kass

unread,

Jul 19, 2002, 2:14:06 PM7/19/02

to

Erin,

There's no limit, but if you will be using many UNIONs, be sure you have
service pack
3 or 4 installed (which is good practice anyway). Microsoft's Knowledge Base
article

FIX: Regression: Compilation of Many UNIONs Takes Excessively Long Time
(Q274706)

indicates that prior to service pack 3, when there are more than about 40,
compilation
time could be excessive. The problem is reportedly fixed with sp3.

Steve Kass
Drew University

Umachandar Jayachandran

unread,

Jul 20, 2002, 11:37:11 PM7/20/02

to

There is a limit. You can only use 256 tables in a SELECT statement. So
the number of SELECT statements is restricted to how many tables you have
used.

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )

Steve Kass

unread,

Jul 21, 2002, 1:38:54 AM7/21/02

to

UC,

This limitation doesn't apply to UNIONs. I was able to run the output of the following
very quickly in 2000, sp2 (after eliminating the one extra UNION ALL).

select
'create table T'+rtrim(orderid-10247)+' (i int)
insert into T'+rtrim(orderid-10247)+' values('+rtrim(orderid)+')'
from northwind..orders

select
'select top 1 '+rtrim(orderid-10247)+' from T'+rtrim(orderid-10247) + ' union all'
from northwind..orders

select
'drop table T'+rtrim(orderid-10247)
from northwind..orders

Steve Kass
Drew University

Steve Kass

unread,

Jul 21, 2002, 1:53:25 AM7/21/02

to

(Of course, it may apply to 7.0, which I notice was mentioned in the original
question.)

SK

oj

unread,

Jul 21, 2002, 2:16:40 AM7/21/02

to

no, it does not apply to sql7.

--
-oj

"Steve Kass" <sk...@drew.edu> wrote in message
news:3D3A4C55...@drew.edu...

Umachandar Jayachandran

unread,

Jul 21, 2002, 3:48:38 AM7/21/02

to

It applies to SQL70 & 2000. It was increased from the previous release.
There has always been some limit or the other for the number of tables you
can use in a SELECT statement.

Umachandar Jayachandran

unread,

Jul 21, 2002, 3:59:11 AM7/21/02

to

Funny, it works for tables. I have always had problems when trying to
mix this with SELECT without FROM. Here is an example where it will fail to
compile:

select top 5000
'select '+rtrim(o1.orderid-10247)+' union all'
from northwind..orders o1, northwind..orders o2

I have hit some problem like this while using XML Schema with ADO and
the massive UNION ALL queries that it is capable of generating. I would
still watch out for the query processor errors while trying something like
this.

Steve Kass

unread,

Jul 21, 2002, 11:25:02 AM7/21/02

to

UC,

The query processor seems to run out of stack space for me at
somewhere between 1300 and 1400 selects. But that's not a precise
limitation on the number of selects or tables, I don't think, and it's
harder to deal with, I suppose, because it's not likely to be predictable.

Steve