256 table limit for partitioned views

I have a partitioned view sitting over several tables and I'm slowly
approaching the 256 number. Can anybody confirm if there is such a
limit for the maximum number of tables that a partitioned view can
hold?

If this is true, does anybody have any suggestions or ideas to work
around this max limit?

TIA!karthik (karthiksmiles@.gmail.com) writes:
> I have a partitioned view sitting over several tables and I'm slowly
> approaching the 256 number. Can anybody confirm if there is such a
> limit for the maximum number of tables that a partitioned view can
> hold?

Yes, since the maximum number of tables per query is 256 I would
expect that there is such a limit.

> If this is true, does anybody have any suggestions or ideas to work
> around this max limit?

How big are your tables? Would it be possible to consolidate them?

In SQL 2005 there is partioned tables, which is taking this to another
level. I don't know how many partitions you can have in a table, but
it's a new ballpark.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||The limit is 256 tables "per SELECT statement", not per query.
Therefore, a UNION query can have more than 256 tables, but
unfortunately, such a query may not be used in a view. For example:

CREATE TABLE T (X INT)
INSERT INTO T VALUES (1)
DECLARE @.SQL varchar(8000)

SELECT @.SQL=ISNULL(@.SQL+' UNION ALL ','')+'SELECT X FROM T'
FROM (SELECT DISTINCT number FROM master..spt_values
WHERE number BETWEEN 0 AND 256) X

--PRINT LEN(@.SQL)
EXEC(@.SQL)

SET @.SQL='CREATE VIEW V AS '+@.SQL
EXEC (@.SQL)

For more informations, see:
http://groups-beta.google.com/group...885c192f511bd1a

Razvan|||Razvan Socol (rsocol@.gmail.com) writes:
> The limit is 256 tables "per SELECT statement", not per query.
> Therefore, a UNION query can have more than 256 tables, but
> unfortunately, such a query may not be used in a view. For example:

Thanks Razvan. I did notice "per SELECT statement", but I was too lazy
to get a practical interpretation of what that really meant.

> For more informations, see:
> http://groups-beta.google.com/group...885c192f511bd1a

That's a useful link!

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Thanks Razvan and Erland...I guess I'm just going to wait for the
Partitioned Tables feature in SQL Server 2005.