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]

Re: 256 table limit for partitioned views

karthik (karthiksmiles@ gmail.com) writes:[color=blue]
> 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?[/color]

Yes, since the maximum number of tables per query is 256 I would
expect that there is such a limit.
[color=blue]
> If this is true, does anybody have any suggestions or ideas to work
> around this max limit?[/color]

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@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Razvan Socol]

Re: 256 table limit for partitioned views

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(@SQ L+' UNION ALL ','')+'SELECT X FROM T'
FROM (SELECT DISTINCT number FROM master..spt_val ues
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]

Re: 256 table limit for partitioned views

Razvan Socol (rsocol@gmail.c om) writes:[color=blue]
> 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:[/color]

Thanks Razvan. I did notice "per SELECT statement", but I was too lazy
to get a practical interpretation of what that really meant.
[color=blue]
> For more informations, see:
> http://groups-beta.google.com/group/...85c192f511bd1a[/color]

That's a useful link!


--
Erland Sommarskog, SQL Server MVP, esquel@sommarsk og.se

Books Online for SQL Server SP3 at

http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[karthik]

Re: 256 table limit for partitioned views

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