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 "partitione d 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.n et

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

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

"laurenq uantrell" <la************ *@hotmail.com> wrote in message
news:11******** **************@ g44g2000cwa.goo glegroups.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 "partitione d view" and a "partitione d table" are different
things. "Partitione d 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