256 table limit for partitioned views

[karthik]

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!

[Erland Sommarskog]

karthik (ka***********@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, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[Razvan Socol]

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/...85c192f511bd1a

Razvan

[Erland Sommarskog]

Razvan Socol (rs****@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/...85c192f511bd1a

That's a useful link!
--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp

[karthik]

Thanks Razvan and Erland....I guess I'm just going to wait for the
Partitioned Tables feature in SQL Server 2005.