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 (karthi...@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, esq...@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/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/microsoft.public.sqlserver.programming/browse_frm/thread/7885c192f511bd1a

Razvan

[Erland Sommarskog]

Razvan Socol (rso...@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/microsoft.public.sqlserver.programming/browse_frm/thread/7885c192f511bd1a

That's a useful link!

[karthik]

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