469,934 Members | 1,750 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,934 developers. It's quick & easy.

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!

Aug 8 '05 #1
4 3307
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

Aug 8 '05 #2
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

Aug 9 '05 #3
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

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

Aug 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

8 posts views Thread by Zero.NULL | last post: by
10 posts views Thread by Sumanth | last post: by
11 posts views Thread by steve.keanie | last post: by
15 posts views Thread by Piero 'Giops' Giorgi | last post: by
reply views Thread by Damir | last post: by
6 posts views Thread by shawno | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.