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

[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...