Using UNION : limitations - SQL Server Forums

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)

Using UNION : limitations

Author

Topic

KenA
Starting Member

28 Posts

Posted - 2005-10-18 : 13:52:00

Hi. When using the UNION command in Sql Server, I´ve notice that there´s some limits like:

SELECT '99232018' AS serie
UNION
SELECT '99232003' AS serie
UNION
SELECT '99231032' AS serie
UNION
.
.
.
.
.
--around 1000 lines
.
.
.
.

I get the following error msg:

Server: Msg 8621, Level 17, State 1, Line 2
Internal Query Processor Error: The query processor ran out of stack space during query optimization.

Is there a limitation in the qty of UNIONs I can run or is it a limitation o server memory?

Or, is there another way (more optimized) that I could make a insert statement to retrieve multiples lines?

Regrads, Ken

»»» Ken.A

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2005-10-18 : 14:02:21

INSERT INTO Table1
VALUES('99232018')
INSERT INTO Table1
VALUES('99231032')
...

Tara

sachinsamuel
Constraint Violating Yak Guru

383 Posts

Posted - 2005-10-18 : 14:05:01

Its nothing to do with UNION vut queries with large amount of arguments cause this error.

Check if this helps

http://support.microsoft.com/kb/q288095/

Regards
Sachin Samuel

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-10-18 : 14:40:14

quote:
Originally posted by KenA

Hi. When using the UNION command in Sql Server, I´ve notice that there´s some limits like:

SELECT '99232018' AS serie
UNION
SELECT '99232003' AS serie
UNION
SELECT '99231032' AS serie
UNION
.
.
.
.
.
--around 1000 lines
.
.
.
.

I get the following error msg:

Server: Msg 8621, Level 17, State 1, Line 2
Internal Query Processor Error: The query processor ran out of stack space during query optimization.

Is there a limitation in the qty of UNIONs I can run or is it a limitation o server memory?

Or, is there another way (more optimized) that I could make a insert statement to retrieve multiples lines?

Regrads, Ken

»»» Ken.A

Why are you trying to do this? You might want to consider storing these values in a table, since databases tend to be good at that sort of thing!

And by the way -- use UNION ALL instead of union unless you are depending on the fact that UNION removes duplicate values. If you don't need this behavoir, a UNION ALL is much more efficient.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-19 : 02:00:34

Also Union will unnecessarily sort the records whereas Union All doesnt do it unless you explicitly use Order By

Madhivanan

Failing to plan is Planning to fail

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources