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.
