Posted by Erland Sommarskog on 06/06/07 21:13

Piero 'Giops' Giorgi (giorgi.piero@gmail.com) writes:
>> You might consider a hybrid solution with 50 individual state tables
>> included in a partitioned view, with each state table partitioned by
>> county. This approach would leverage partitioning to quickly reload
>> individual counties yet provide a seamless view of the entire country.
>
> That is exactly what I want to do, but unfortunately I'm not (YET)
> able to do it.
>
> How can I have a partitioned view of partitioned tables?
> I have the 50 state tables partitioned by county, but I can't get to
> the next step.
>
> Can someone post a small example of the thing?

To me that sounds like a managability nightmare. While you can query
the beast in one query, when you need to flush the rows for Orange
County, you would have to explicitly to go to the CA table to
switch partitions, which would mean a lot of dynamic SQL.

I don't know if there is any catch with partition views over partitioned
tables (I really need to find some time to play with partitioned tables
to learn them!), but in a normal partitioned view you would have:

CREATE TABLE CA (state char(2) DEFAULT 'CA' CHECK (state = 'CA'),
-- other columns
PRIMARY KEY (state, county, whatever))

CREATE TABLE RI (state char(2) DEFAULT 'RI' CHECK (state = 'RI'),
...

CREATE VIEW thewholebunch AS
SELECT state, county, .....
FROM CA
UNION ALL
SELECT state, county, .....
FROM RI
....

But personally I would look into make the merging of new files more
effective than just dropping all existing rows.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Back to original message]