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.
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
Anith Sen wrote in message ...
Kresimir Radosevic
"Anith Sen" <an...@bizdatasolutions.com> wrote in message
news:uyr7Dt0LCHA.2088@tkmsftngp11...
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
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )
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
SK
--
-oj
"Steve Kass" <sk...@drew.edu> wrote in message
news:3D3A4C55...@drew.edu...
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.
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