Maximum UNION statements in a query

[laurenq uantrell]

Wondering if there is a physical or realistic limitation to the number
of UNION statements I can create in a query? I have a client with
approx 250 tables - the data needs to be kept in seperate tables, but I
need to be filtering them to create single results sets. Each table
holds between 35,000 - 150,000 rows. Should I shoot myself now?

lq

[Razvan Socol]

Hello, lq

There is a limit of "256 tables per SELECT statement". You can use more
than 256 tables in a query with UNION statements, but such a query
cannot be used as a view or as a subquery for a SELECT statement. If
you use such a query directly, you may hit a stack space limit of the
query optimizer at around 1300-1500 SELECT-s.

If you implement partitioned views, make sure that you stay under the
256-tables limit. It would be best if the partitioning column has a
fixed set of possible values (for example, use the first letter of the
country name, not the country name itself).

You may also want to take a look at "partitioned tables" in SQL Server
2005. They are somehow easier to implement and more flexible than
partitioned views. Also, they are subject of a limit of "1000
partitions per partitioned table" (instead of the 256-table limit for
partitioned views).

For more informations, see:
http://groups.google.com/group/comp....e778a5f3affd5b
http://groups.google.com/group/micro...5402088c4da967
http://msdn2.microsoft.com/en-us/library/ms190199.aspx
http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Razvan

[Mike Epprecht \(SQL MVP\)]

Hi

Shoot yourself.

SQL Server 7.0, 2000 and 2005 limits are as follows:
Tables per SELECT statement: 256

So, not only will your queries perform badly, but you walk into a 256 table
limit. No matter if you have views etc, but 256 base tables is all you get.

With correct design, you could have all the data in one table and access the
data through views to enforce security.

Regards
--------------------------------
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland

IM: mi**@epprecht.net

MVP Program: http://www.microsoft.com/mvp

Blog: http://www.msmvps.com/epprecht/

"laurenq uantrell" <la*************@hotmail.com> wrote in message
news:11**********************@g44g2000cwa.googlegr oups.com...

Wondering if there is a physical or realistic limitation to the number
of UNION statements I can create in a query? I have a client with
approx 250 tables - the data needs to be kept in seperate tables, but I
need to be filtering them to create single results sets. Each table
holds between 35,000 - 150,000 rows. Should I shoot myself now?

lq

[laurenq uantrell]

Mike,
Thanks for that. Unfortunately, the client architecture rules require
the data to reside in approx 250 different tables.

So, what I'm considering, before I shoot myself, is playing with
something like this (though I suspect the performance will totally blow
with each table holding average of 50K records.):

(foo code)

DECLARE @counter smallint, @tablename varchar(20), @sql nvarchar(4000)
SELECT @tablename = 'tblBaseName'

SELECT @sql = 'SELECT * FROM tblBaseName1'

SET @counter = 2
WHILE @counter < 251 /*max table count*/
BEGIN
SET NOCOUNT ON
@tablename = @tablename + CAST(@counter as varchar(3))
SELECT @sql =

@sql + ' UNION ALL SELECT * FROM ' + @tablename

SET @counter = @counter + 1
SET NOCOUNT OFF
END
GO

EXEC sp_executesql @sql

[laurenq uantrell]

Never worked with partitioned views before (and not too old to learn
new tricks) but I understand from Books Online that a partioned view
requires a partioned table???
This client is using SQL Server 2000

[Doug]

build a 'temp' table, and union them in one or 10 at a time, then do
your work against the temp table.

will take a little work to make the whole mess re-entrant, but can be
done by creating the temp table name as unique and passing its name
around as a variable.

[Razvan Socol]

> I understand from Books Online that a partioned view

requires a partioned table???
This client is using SQL Server 2000
No, a "partitioned view" and a "partitioned table" are different
things. "Partitioned table"-s work only in SQL Server 2005. If you use
SQL Server 2000, you can use a partitioned view. See:
http://msdn.microsoft.com/library/en...es_06_17zr.asp
Unfortunately, the client architecture rules require
the data to reside in approx 250 different tables.

For what purpose ? Performance ? Security ? You should evaluate if the
original reason (for this architecture) is still accomplished best in
this way or if there are another ways to do it.

Razvan

[laurenq uantrell]

Security, and I can have no control over that structure for this
particular client.

[Razvan Socol]

If security was the purpose, you need to consider what rights will be
granted for this partitioned view, so it won't defeat the purpose of
creating the separate tables. After you do this, you should consider
creating a single table that contains the data that would be returned
by the view and create views instead of each old table (using "WITH
CHECK OPTION"). If you do this, you should be able to enforce the same
security policies, but you would have the data in only one place.

Razvan

[Stu]

If you use partitioned views with CHECK constraints on the tables, your
performance will actually be pretty good. We store about 4 million
rows of data a day, and we keep a partioned view of about 90 days worth
of data; works fine, as long as you supply the appropriated check
constraint to the view when searching. Another benefit is that the
partioned view can be used to UPDATE and INSERT data.

There are some gotcha's (like passing a parameter to the constraint),
but for the most part, partitioned views sound like the solution.

Stu

[laurenq uantrell]

And the partitioned view performs better than just a sproc that limits
the range, but without the parameter issues?

[laurenq uantrell]

Yes. I started doing this already. Thanks for the ideas on this.
When it's all in one table, and users are running select queries
against this table (no updates, no inserts and no deletes) will
performance be effected if multiple users are running similar select
queries against this table at the same time?

[laurenq uantrell]

Yes, I have started doing this already. A question, if multiple users
are running select queries against this table (not inserts, not updates
and not deletes) will performance decrease with the number of users
running select queries at the same time - most queries will probably
return 1-350 rows?

[Razvan Socol]

If the queries return less than 350 rows (less than 0,01% of the big
table), it's probable that some index may be used, if it exists.
In this case, the performance impact should be insignificant.

If the query returns much more rows (or if an appropriate index does
not exist) SQL Server may perform a table scan. In this case, scanning
one big table (if you use a big table, and 250 views) would be slower
than scanning one (or a few) smaller tables by querying the partitioned
view, also specifying a condition on the partitioning column (if you
use 250 tables and a partitioned view).

Razvan

[Stu]

To be honest, I don't know. I never considered doing it the other way,
because conceptually, by using the partiitioned view, my application is
only dealing with one entitiy. It just seemed easier to manage than
building intelligence into my INSERT and UPDATE statement to determine
where the data belongs. I just INSERT into the view, and the check
constraints handle the slicing and dicing. If I need to archive a
day's worth of data, I copy the appropriate table to my archive, drop
the table, and rebuild the view.

Of course, having re-read this thread, I'm not 100% sure that
partitioned views will support the number of tables you need. One of
the limitations of using my method of dynamically building the view is
that the view statemnt is a varchar(8000) statement; if there an
excessive number of tables, the view may not be built correctly. I use
a similar method to the code snippet you posted above; the base SQL
statement you posted has about 40 characters in. 40 * 251 = 10040, so
the snippet you suggest will probably run into the same issue.

I have seen some tricks to get around this limitation of dynamic SQL
(like EXEC (@sql1 + @sql2), but haven't tried them; there are solutions
to every problem, however. The questions I would ask myself would be:

1. How often am I going to add new tables to this structure?
2. What's the easiest way to manage interaction with the data?
3. What's the differentiation between the slices?
4. How often will I need to retrieve information from multiple slices
at once?

Just thinking aloud.

[laurenq uantrell]

I just finished running un update query on this table - which currently
6.5 is million rows. The update query inserted a single digit into a
tinyint column for every row based on a join on a char(1) column in the
table -- this update query took 14 1/2 hours!!!!
I'd call this unworkable...