Posted by Razvan Socol on 12/17/05 09:46

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.databases.ms-sqlserver/browse_frm/thread/64e778a5f3affd5b
http://groups.google.com/group/microsoft.public.sqlserver.programming/browse_frm/thread/f65402088c4da967
http://msdn2.microsoft.com/en-us/library/ms190199.aspx
http://msdn2.microsoft.com/en-us/library/ms143432.aspx

Razvan

• Next in forum: Re: How to pass table names to a Stored procedure
• Prev in forum: Re: Maximum UNION statements in a query
• Thread view: Re: Maximum UNION statements in a query

[Reply to this message]